Search code examples
sqlsql-serversortingcase

Case expression in Order By processing order for rows

Sample data:

CREATE TABLE demo_table
(
    ID       INT,
    NAME     VARCHAR(20),
    APPLE    INT,
    ORANGE   INT
);

INSERT INTO demo_table 
VALUES (1, 'John', 45,  7),
       (2, 'Greg', 20, 25),
       (3, 'Alex', 22, 29),
       (4, 'Paul', 50, 55),
       (5,  'Bob', 40, 13);

I'm trying to understand the output of the following query.

SELECT * 
FROM demo_table
ORDER BY 
    CASE 
        WHEN APPLE < ORANGE 
            THEN APPLE
        ELSE ORANGE
    END, 
    name DESC

Output:

ID NAME APPLE ORANGE
1 John 45 7
5 Bob 40 13
2 Greg 20 25
3 Alex 22 29
4 Paul 50 55

How does the processing work for the order by with a case expression?

The first row of the result makes sense.

I'm not understanding the second row of the output -- shouldn't it be the row with ID = 2? (Since apple < Orange clause is satisfied)?

Solution

  • What you're ordering by is this column ASC and this is why ID 5 appears second

    demo
7
13
20
22
50

    fiddle here