How to sort top of N records after selecting?

Question

I'm using AdventureWorks2019 to practise. I want to select top 10 values of FirstName column inside Person.Person table, after getting the values, I want to sort them by ascending.

When I've tried to select top 10 values, I've got result:

Syed
Catherine
Kim
Kim
Kim
Hazem
Sam
Humberto
Gustavo
Pilar

I want to sort them to:

Catherine 
Gustavo 
Hazem 
Humberto 
Kim 
Kim 
Kim 
Pilar 
Sam 
Syed

This is what I've tried, but failed:

select top 10 [FirstName] from Person.Person order by [FirstName];

and

select * from
    (select top 10 [FirstName] from Person.Person) as persons
order by [FirstName];

and

with persons as (select top 10 [FirstName] from Person.Person)
select * from persons order by [FirstName];

Answer 1

When you don't specify an ORDER BY, sql server will return the values in whatever order is convenient, there is no guarantee it'll stay the same on subsequent executions. So a TOP(x) without an ORDER BY is usually senseless, because those x can be any x rows.

If you really want to sort those random 10 rows, you should use a sub query as you did. You can see sql server is free to choose any order it sees fit, because you didn't tell it what you want.

If you really, really want to sort those rows, you can try to insert them into a temporary table or table variable first. But again, I suspect you'll find sql server will decide on 10 different rows.

A nice way to visualize it (sort of) is to look at the execution plan: no ORDER BY means no sort, so the rows are shown in the order that they come in (things get a tad mythical beyond that).