CODEWITHSUNDEEP
sqlsql-serversql-order-by
siva Rapolu
siva Rapolu

Reputation: 409

ORDER BY and TOP statements in SQL works different on listing down the records and why?

I am using table called CITY from my database with below city names. CITY Names : Delhi, Mumbai, Patna, Vijayawada, Panaji, Tiruvananthapuram, Chennai and Kolkata.

ORDER BY:

Select NAME from CITY ORDER BY LEN(NAME)

Output : Delhi, Patna, Panaji, Mumbai, Kolkata, Chennai, Vijayawada, Tiruvananthapuram

TOP 3 and ORDER BY

Select TOP 3 NAME from CITY ORDER BY LEN(NAME)

Output : Delhi, Patna, Mumbai

My question here is, why not Delhi, Patna and Panaji. Why top 3 is picking the fourth item instead of third one?

Upvotes: 0

Views: 60

Answers (2)

vibhor vaish
vibhor vaish

Reputation: 163

This was one of the reasons WITH TIES keyword was introduced along with TOP clause.

Without using the keyword, you would obtain random results for the results you hit the match with. As you see here, Mumbai and panaji had same length so either of them could have been returned. While using the keyword, you would have seen 4 results return (both of them included).

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272096

This is because Panaji and Mumbai are both 6 characters long and both of them qualify for the third position inside the TOP 3 clause. SQL Server is free to return Panaji or Mumbai without violating the ORDER BY criteria and there is absolutely no guarantee which city it will return at any given time.

To handle ties you can explicitly specify additional criteria in ORDER BY clause e.g.:

ORDER BY LEN(name), name -- order by length
                         -- if there is a tie then by name

Upvotes: 3

Related Questions