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 * FROM(
( SELECT * FROM (
select CITY, LENGTH(CITY) K
FROM STATION
WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY))
FROM STATION )
ORDER BY CITY ASC )
WHERE ROWNUM=1
) UNION
(SELECT * FROM (
select CITY, LENGTH(CITY) K
FROM STATION
WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY))
FROM STATION )
ORDER BY CITY ASC )
WHERE ROWNUM=1
)
);
Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1;
Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
why write "city asc limit 1" rather than just "asc limit 1"? It seems like this made a difference in getting the right answer, but not sure what change that made
It works on MySQL , once I read your answer I know I thought too much,
I just need to write two independent queries then it will output the answer. I don't have to just write one query to output this.
select city ,length(city)
from station
where length(city)=
(select min(length(city)) from station order by length(city) asc);
select city,length(city) from station where length(city)=(select max(length(city))from station order by city asc);
the output is
Roy-3
Amo -3
Lee -3
Marine On Saint Croix 21
but i got the logic of what you have written a thumbs up...
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1)
UNION
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)
I just added union to your code and put the select statements in separate brackets and it worked. Thanks!
(Select city, LENGTH(city) from station Order By Length(city), city asc limit 1) union
(Select city, LENGTH(city) from station Order By Length(city) desc, city desc limit 1);
[Second Query]Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
Here city should be asc as we need to sort city in ascending order of their occurance.
so final query
Select City,char_LENGTH(City) from Station Order By Length(City) desc, City asc limit 1;
(Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1)
union all
(Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1);
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
I am also getting error for using LIMIT statements.
for c#
select distinct top 1 city,LEN(city) from station order by len(city);select distinct top 1 city,LEN(city) from station order by len(city) desc;
This works, but I thought for a moment that in the case where a result set includes cities with the same length the query might fail if you don't do
order by citySay the result set looks like this:
Then wouldn't
select distinct top 1 city,LEN(city) from station order by len(city)return Bb instead of Aa.
So order by,
for min, .... order by len(city), city
for max, ... order by len(city) desc, city
true for mysql.. not oracle... would u know how to write this for oracle? Thanks
use length() instead of len() in ms sql server.
LEN() is used in sql server, too.
SELECT * FROM( ( SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) UNION (SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) );
WORKING...BUT EXPLAIN
/* FOR FIRST QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1;
/* FOR SECOND QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY DESC LIMIT 1;
great. . .It works fine. .Thnks
You don't need to put the first ORDER BY cause by default it's asc.
Try this:
Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1; Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
why write "city asc limit 1" rather than just "asc limit 1"? It seems like this made a difference in getting the right answer, but not sure what change that made
I think if you erase "city" from "city asc limit 1" then it will not maintain the alphabetical order.
no need for the asc and desc in the "city asc limit 1", that won't do a thing. cmiiw
Yes, that is correct. That's why it happens.
It works on MySQL , once I read your answer I know I thought too much, I just need to write two independent queries then it will output the answer. I don't have to just write one query to output this.
what if out of all the cities with the same minimum/maximum length , we wanted to select the city that came last in the alphabetical order ?
but i tried in this way:
select city ,length(city) from station where length(city)= (select min(length(city)) from station order by length(city) asc);
Try This
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1) UNION (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)
it works
Why LIMIT 1?
So that only one value gets printed.
but we also have to print cities of same lenghth! so will it work for that?
Since you just need to print one of the shortest or longest item.
this doesn't work in oracle. is there any keyword 'limit' in oracle cause i have serched and didn't found any??
Thats what im looking for bro. Beside "where rownum <= num" which wont work for this because of all the other stuff
Thanks man!!
not working
I just added union to your code and put the select statements in separate brackets and it worked. Thanks!
(Select city, LENGTH(city) from station Order By Length(city), city asc limit 1) union (Select city, LENGTH(city) from station Order By Length(city) desc, city desc limit 1);
[Second Query]Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
Here city should be asc as we need to sort city in ascending order of their occurance. so final query Select City,char_LENGTH(City) from Station Order By Length(City) desc, City asc limit 1;
This is more clean. Thanks!!
(Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1) union all (Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1);
Same