Shortest and longest length city | SQL Server |SQL Interview Preparation | Part 48

Shortest and longest length city in sql

Please share videos to all your friends

For SQL Quiz-

Find Us On FaceBook-

-----Query
IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='STATION')
BEGIN
DROP TABLE STATION
END

CREATE TABLE STATION
(
ID INT,
CITY VARCHAR(21),
STATE VARCHAR(10),
LAT_N NUMERIC(14,8),
LONG_W NUMERIC(14,8)
)
INSERT STATION VALUES (1,'Noida','UP',1.025,2.365789),(2,'Kanpur','UP',5.36987,5.369874),
(2,'Unnao','UP',1.12567,6.354789),(4,'Pune','M',2.36548,3.012548),
(5,'NAGPUR','M',3.36548,5.012548),(6,'ROME','XX',7.36548,8.012548)

;with cte1
as
(
SELECT top 1 city,LEN(CITY) as len_city
FROM STATION
order by LEN(city),CITY
),cte2
as
(
select top 1 CITY,len(city) as len_city
from STATION
order by len(city) desc,CITY
)
select * from cte1
union all
select * from cte2

[prashantjha]

Nice Video!!
We can also use:


select City, LEN(City) as LENGTH from Station where LEN(City)= (select MAX(LEN(CIty)) from Station)
UNION
ALL
select City, LEN(City) as LENGTH from Station where LEN(City)= (select MIN(LEN(City)) from Station)

[lokeshsharma]

Commendable job, the best way to teach, thank you so much, kindly keep posting more on SQL and Tableau, if possible on EXL as well

[ahmed_abdoamin_abdo]

Thank you! 🙂This video is very helpful.

[shwetajain]

Can't we use ROW_NUMBER() OVER (PARTITION BY LEN ORDER BY LEN, CITY) AS RW and put a filter on RW in WHERE Clause like WHERE RW = 1?

[shefali]

Sir, please help me understand what is cte?

[bilalquasim]

is it mandatory to write - as len_city.

[adistry]

top is not a valid keyword in mysql?
I am getting syntax error, why so?