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 MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) limit 1;
SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) desc limit 1;
If you have a rownum (i.e. Oracle) you can't use it in one query with order by, the rownum is assigned before order by is applied. You can wrap it in another query and use rownum there, or use row_number, or switch db.
select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc;
select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;
Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.
Weather Observation Station 5
You are viewing a single comment's thread. Return to all comments →
EVEN THIS QUERY IS GIVING WRONG ANSWER....SOMETHING IS WRONG
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ROWNUM <= 1 ORDER BY LENGTH(CITY);
SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) limit 1; SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) desc limit 1;
this code was a modification of onthekineguy
thank you it works for me
could you tell me how "MIN(CITY)" works? How could a STRING use MIN function?
If you have a rownum (i.e. Oracle) you can't use it in one query with order by, the rownum is assigned before order by is applied. You can wrap it in another query and use rownum there, or use row_number, or switch db.
The problem is that rownum limits your rows before order by is executed. Use FETCH FIRST mechanism i described in comment above.
If you want use rownum, you should rewrite code like this
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;
This is the best answer for MYSQL, thanks
I customised it for MS SQL Server as below:
DECLARE @V1 VARCHAR(MAX)
DECLARE @V2 VARCHAR(MAX)
DECLARE @V3 VARCHAR(MAX)
DECLARE @V4 VARCHAR(MAX)
SET @V1 = (SELECT MIN(LEN(CITY)) FROM STATION);
SET @V2 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V1 ORDER BY CITY );
SET @V3 = (SELECT MAX(LEN(CITY)) FROM STATION);
SET @V4 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V3 ORDER BY CITY );
SELECT @V2,@V1; SELECT @V4,@V3;
select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc; select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;
Thank you
Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.
Without use of variables (MySQL):
Hi, I'm wondering - why does WHERE LENGTH(CITY) = MAX(LENGTH(CITY)) not work? I get a "Invalid use of group function" error.
Double check that you're not using Oracle, but that you're using MySQL
That's because you need to use 'having' when you use a group function like max or length
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY LIMIT 1;
what the is use of writing city before limit???
It works, but also add city to order by list
Simple and elegant. Nice.