We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
select city, length(city) from station order by length(city) asc, city asc limit 1;
select city, length(city) from station order by length(city) desc, city asc limit 1;
ive changed the code and it still works.
select city, length(city) from station order by length(city) asc, city limit 1;
select city, length(city) from station order by length(city) desc, city limit 1;
we can also write this query in MySql as
select city, length(city) from station order by length(city) limit 1; select city, length(city) from station order by length(city) desc limit 1;
'asc' stands for ascending order.
The query works fine even when 'asc' is removed from the query because the default order for string is alphabetical(first words starting with 'a' then 'b',...) which is exactly what this question demands.
TOP in Orcale will be in the WHERE clause AS "WHERE ROWNUM = N".
This one is a big catch because each SGBD works differently with the TOP concept.
For better details, see http://www.w3schools.com/sql/sql_top.asp
All the best!
-- Oracle solution --
SELECT * FROM
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY)
WHERE ROWNUM = 1
UNION
SELECT * FROM
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY)
WHERE ROWNUM = 1;
This query is about unifying the results of two subqueries, so we use the UNION clause.
The WHERE ROWNUM = 1 in both subqueries is there in order to do a "TOP 1". That is the way this is done in Oracle. It is not very didactic but that's how Oracle undestands it. A simple explanation for that is here: https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle
The reason of what I have put in the ORDER BY, I think it is due to the statement of the challenge, but I'd have to look into it again.
Thank you! None of the previous codes worked and I really has trying to do this for 3 days already, while for my codes there was always the "SQL query not ended properly" error.
Good Approach but when we are asked to display as single result set . This works
-- creating sample STATION table and inserting value
create table STATION(id int , city varchar(50));
insert into STATION(id,city) values (1,'madras'),(2,'Salem'),(3,'Trichy'),(4,'Ambur'),(5,'Velur');
-- CTE table with extra colum having city string length
with AddedLengthCol
as
(
select city, len(city) as lencount from STATION
)
-- Used Rank_Dense to sove it .
select s.city , s.lencount,rank1 from (select city , lencount , DENSE_RANK() over (PARTITION BY lencount order by city ) as rank1 from AddedLengthCol) as s
where s.rank1=1 and ( s.lencount = (select max(lencount) from AddedLengthCol) or s.lencount = (select min(lencount) from AddedLengthCol))
/Better Solution SQL SERVER:/
select min(city),len(min(city)) from STATION
union
select min(city),len(min(city)) from STATION where len(city) =(select len(max(city)) from STATION)
I went with the CTE, but it doesn't work when I run it here. On SQL Server it returns the answer properly:
with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen desc;
with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen asc
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
Answer in MS SQL SERVER:
SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY),CITY; SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC,CITY;
I think it work only for ms sql sever2012 but won't work for oracle, Is there anything like "TOP" in oracle.
in oracle you can use WHERE ROWNUM = 1 instead
Try this:
select city, length(city) from station order by length(city) asc, city asc limit 1; select city, length(city) from station order by length(city) desc, city asc limit 1;
ive changed the code and it still works. select city, length(city) from station order by length(city) asc, city limit 1; select city, length(city) from station order by length(city) desc, city limit 1;
why did you put 'asc' limit 1
we can also write this query in MySql as select city, length(city) from station order by length(city) limit 1; select city, length(city) from station order by length(city) desc limit 1;
'asc' stands for ascending order. The query works fine even when 'asc' is removed from the query because the default order for string is alphabetical(first words starting with 'a' then 'b',...) which is exactly what this question demands.
YES,very helpful!
sir can you explain this code and also what's the use of 'limit 1'? thanks in advance
no, instead you can use rownum in oracle
TOP in Orcale will be in the WHERE clause AS "WHERE ROWNUM = N". This one is a big catch because each SGBD works differently with the TOP concept. For better details, see http://www.w3schools.com/sql/sql_top.asp All the best!
-- Oracle solution -- SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY) WHERE ROWNUM = 1 UNION SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY) WHERE ROWNUM = 1;
SIR CAN YOU EXPLAIN THIS PLEASE
SET @V1 := (SELECT MIN(LENGTH(CITY)) FROM STATION); SET @V2 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V1 ORDER BY CITY LIMIT 1);
SET @V3 := (SELECT MAX(LENGTH(CITY)) FROM STATION); SET @V4 := (SELECT CITY FROM STATION WHERE LENGTH(CITY) = @V3 ORDER BY CITY LIMIT 1);
SELECT @V2,@V1; SELECT @V4,@V3;
PLease explain this
Thanks dhananjay.. it worked :-)
I second it. Can you please explain this.
Hello Deep...
This query is about unifying the results of two subqueries, so we use the UNION clause.
The WHERE ROWNUM = 1 in both subqueries is there in order to do a "TOP 1". That is the way this is done in Oracle. It is not very didactic but that's how Oracle undestands it. A simple explanation for that is here: https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle
The reason of what I have put in the ORDER BY, I think it is due to the statement of the challenge, but I'd have to look into it again.
All the best!
thanks sir it helps
may you please explain this to me to on how to understand it.
Thank you! None of the previous codes worked and I really has trying to do this for 3 days already, while for my codes there was always the "SQL query not ended properly" error.
YES IN ORACLE TOP IS PRESENT..
This is two different queries. Does it accept this?
Answer in MS SQL SERVER:
Good Approach but when we are asked to display as single result set . This works
-- creating sample STATION table and inserting value create table STATION(id int , city varchar(50)); insert into STATION(id,city) values (1,'madras'),(2,'Salem'),(3,'Trichy'),(4,'Ambur'),(5,'Velur');
-- CTE table with extra colum having city string length with AddedLengthCol as ( select city, len(city) as lencount from STATION )
-- Used Rank_Dense to sove it . select s.city , s.lencount,rank1 from (select city , lencount , DENSE_RANK() over (PARTITION BY lencount order by city ) as rank1 from AddedLengthCol) as s where s.rank1=1 and ( s.lencount = (select max(lencount) from AddedLengthCol) or s.lencount = (select min(lencount) from AddedLengthCol))
/Better Solution SQL SERVER:/ select min(city),len(min(city)) from STATION union select min(city),len(min(city)) from STATION where len(city) =(select len(max(city)) from STATION)
I went with the CTE, but it doesn't work when I run it here. On SQL Server it returns the answer properly: with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen desc; with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen asc
this one works well, though I didn't know it could be 2 queries...tricky!
That was my answer too. Is there a way yo improve it?
This is correct answer for MS Sql Server. Thank You.
THANK YOU
I had gotten it almost right, but I had no idea I was supposed to actually use Order By to get the lowest and highest numbers.