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!

Answer 1

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