ORDER BY not working properly in SQL Server

Question

I have a problem that ORDER BY is not working the way I want.

My code:

SELECT 
    LastName + ' ' + FirstName AS [Full name],
    TitleOfCourtesy AS titleOfCourtesy,
    CASE
        WHEN TitleOfCourtesy IN ('Ms.', 'Mrs.') 
            THEN 'Female'
        WHEN TitleOfCourtesy = 'Mr.' 
            THEN 'Male'
    END AS Sex
FROM 
    Employees
WHERE 
    TitleOfCourtesy IN ('Mrs.','Ms.','Mr.')
-- ORDER BY Sex DESC;

This code returns this result set:

When I add ORDER BY(uncomment last line), it returns:

I think result should be like this (this is what I want):

Here is my Employees table:

I don't understand why Callahan Laura and Dodsworth Anne is moving up in img 2. What happened? Did I misunderstand how ORDER BY works? Any help is appreciated!

You need `ORDER BY Sex DESC, FullName ASC` if it is important to you to get that ordering. If you have ties for the value you are ordering by SQL Server is free to return them in any order (as it also is for queries with no `ORDER BY` so your first query has no guaranteed ordering either) — Martin Smith, Mar 10 '23 at 15:59
@MartinSmith you can see in pic 3, i only want moving all ```Male``` to top and not sort by name — Nguyễn Văn Mạnh, Mar 10 '23 at 16:02
If you care about the results being ordered in some specific way there needs to be some values in the data that you can use in an `ORDER BY` clause that guarantees that. The ordering of your first resultset isn't some "natural" ordering. It is just whatever order the execution plan happened to deliver the rows in — Martin Smith, Mar 10 '23 at 16:03
@MartinSmith i was add 1 pic about ```Employees``` table, and i think if sort by sex it will return pic 3, but not now. — Nguyễn Văn Mạnh, Mar 10 '23 at 16:10
If you go to a web site and say "I want all of today's news, ordered by date." If there are 5 stories today and they all only have a _date_, what does "ordered by date" mean? How should the web site know what order you expect if you don't tell it what else to order by? If you expect it to _then_ be ordered by alphabetical, or reverse alphabetical, or category, or length, you need to tell it that by saying "ordered by date and _then_ that other thing." SQL Server doesn't necessarily think like you unless you tell it how to do that. — Aaron Bertrand, Mar 10 '23 at 16:13
It has always appeared to me that if no `ORDER BY` is specified, SQL Server will return results in the order of the primary key, especially so if this is the clustered index. This behaviour however is undocumented and by no means part of the SQL standard and is therefore not to be relied upon.... — Jonathan Willcock, Mar 10 '23 at 16:16
.... As soon as you specify an `ORDER BY` all such default behaviour goes out of the window. In fact what SQL Server appears to be doing here is to scan the table in physical clustered order. Any values that are out of place according to the specified `ORDER BY` are being extracted and added at the end of the batch of equal ranks to which they belong. This is entirely legitimate behaviour within the rules of the SQL standard. It may not be what you wanted, but it is not wrong. — Jonathan Willcock, Mar 10 '23 at 16:16
@JonathanWillcock That is a common observation but it is only that. If the query is covered by a different index, that will be used instead. And then other things can affect the order without order by (columnstore and in-memory, for example, parallelism / batch mode). [See #3 here](https://www.sentryone.com/blog/aaronbertrand/t-sql-tuesday-56-sql-server-assumptions#d584c2). — Aaron Bertrand, Mar 10 '23 at 16:19
@JonathanWillcock - probably in this case with no ORDER BY they are getting a clustered index scan in order of EmployeeId but equally they could get an index seek on `TitleOfCourtesy` and that would no longer be ordered in that order. Even with a clustered index scan it can be ordered or unordered (and get an allocation ordered scan or merry go round scan as well as what Aaron mentions above) — Martin Smith, Mar 10 '23 at 16:19
If you want the ephemeral sort, you can select data without sort into a temp table with an identity, and then select from that table ordered by sex desc, identityValue ASC. Then, if you're lucky, you will get that weird sort you're looking for. — siggemannen, Mar 10 '23 at 16:21
Thank you, I was thinking too simple about how ```ORDER BY``` work, i chose ```Male``` and ``Female``` separately and concatenated them by ```UNION ALL```, it will return my answer. — Nguyễn Văn Mạnh, Mar 10 '23 at 16:27
eh. You shouldn't do that and clearly haven't really taken much on board of the above. As I said above it looks like you just need `ORDER BY Sex DESC, EmployeeId ASC` - the `UNION ALL` approach leaves you reliant on non guaranteed behaviour again. If you don't have an `ORDER BY` clause that defines the required ordering for every row in the result set that you care is in a specific ordering then SQL Server is free to return a resultset in a different ordering than you were hoping — Martin Smith, Mar 10 '23 at 16:28
@MartinSmith thank you, now i clear, stupid that i used ```UNION ALL``` to join them, i can make it by add condition ```EmployeeID``` — Nguyễn Văn Mạnh, Mar 10 '23 at 16:37
@NguyễnVănMạnh, `UNION ALL` is the not the correct answer, unless you also added `ORDER BY` for both columns. Even if the order is as you expect today, it may change tomorrow. — Dan Guzman, Mar 10 '23 at 16:50
"i only want moving all Male to top and not sort by name" -- That's not how relational databases work. The rows are not really sorted in the absence of an `ORDER BY` clause. The ordering you see is just a coincidence. — The Impaler, Mar 10 '23 at 16:52
@TheImpaler my mistake, i was think it will sort with ```Sex```, after return result with same order as in the original table but I wrong. — Nguyễn Văn Mạnh, Mar 10 '23 at 17:03

score 4 · Accepted Answer · answered Mar 10 '23 at 16:43

Your initial resultset looks like it is ordered by EmployeeID.

This is not guaranteed and is just an artefact of the execution plan that SQL Server used to get the rows. To be clear without any explicit ORDER BY any ordering of those rows would be equally correct.

When you do ORDER BY Sex DESC you get exactly what you asked for. All the "Male" rows are ordered first and then the "Female" - within each group SQL Server is free to order them in any way.

It looks like you are wanting EmployeeID to be used as a secondary sorting criteria so you need to use

ORDER BY Sex DESC, EmployeeId ASC

to get that

ORDER BY not working properly in SQL Server

1 Answers1