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