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.
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
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..