SQL Server Case expression in Order By processing order for rows

rslzwgfq 于 2023-05-28 发布在其他

关注(0)|答案(2)|浏览(134)

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

sql-server

来源：https://stackoverflow.com/questions/76343567/case-expression-in-order-by-processing-order-for-rows

2条答案

按热度按时间

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

赞(0）回复(0）举报 2023-05-28

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

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server Case expression in Order By processing order for rows

2条答案

相关问题

热门标签

最新问答