SQL Server Case expression in Order By processing order for rows

rslzwgfq  于 2023-05-28  发布在  其他
关注(0)|答案(2)|浏览(132)

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:

IDNAMEAPPLEORANGE
1John457
5Bob4013
2Greg2025
3Alex2229
4Paul5055

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)?

kr98yfug

kr98yfug1#

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

vlurs2pr

vlurs2pr2#

The CASE expression in the order by evaluates only the first record for chosing the column to order, the select statement order by ORANGE ASC. Next select statement would do the same:

SELECT *  
FROM demo_table  
ORDER BY ORANGE,   
         name DESC

相关问题