0

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:

enter image description here

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

enter image description here

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

enter image description here

Here is my Employees table:

enter image description here

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!

Improve this question
17
  • 2
    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) Commented Mar 10, 2023 at 15:59
  • 1
    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 Commented Mar 10, 2023 at 16:03
  • 1
    I think you want ORDER BY Sex DESC, EmployeeId ASC then Commented Mar 10, 2023 at 16:09
  • 1
    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. Commented Mar 10, 2023 at 16:13
  • 1
    .... 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. Commented Mar 10, 2023 at 16:16

1 Answer 1

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.