CODEWITHSUNDEEP
sqlsql-serversql-order-by
user11224591
user11224591

Reputation:

Use case after order by

I was reading an sql book, one of questions is:

Write a query against the Sales.Customers table that returns for each customer the customer ID and region. Sort the rows in the output by region, having NULL marks sort last (after non-NULL values).Note that the default sort behavior for NULL marks in T-SQL is to sort first (before non-NULL values).

enter image description here

And the answer is :

SELECT custid, region
FROM Sales.Customers
ORDER BY
 CASE WHEN region IS NULL THEN 1 ELSE 0 END, region;

I can kind of get the idea but still confused, let's take the record with custid = 9 for instance: since custid 9 has a null region, in the case cstatement return 1, so the query is sth like:

ORDER BY 1, region

which is equivalent to:

ORDER BY custid, region  --because custid is the first column

so how come the custid 9 is not before custid 10(the second record in the output)? isn't that output needs to order by custid first, so 9 is before 10?

Upvotes: 1

Views: 657

Answers (5)

sticky bit
sticky bit

Reputation: 37472

ORDER BY CASE
           WHEN region IS NULL THEN
             1
           ELSE
             0
         END,
         region

is not equivalent to

ORDER BY 1,
         region

because in the second one the first column to sort by is always constant, whereas in the first it can change depending on the CASE.

And

ORDER BY 1,
         region

is also not equivalent to

ORDER BY custid,
         region

again in the first the 1 is constant but custid is variable.

What

ORDER BY CASE
           WHEN region IS NULL THEN
             1
           ELSE
             0
         END,
         region

does is to "generate" a new column to sort by depending on the content of region. That new column gets 1 when region is null 0 otherwise. If you imagine this new column in the table it would look like

custid | region | new column
...
10     | BC     | 0
...
9      | NULL   | 1
...

Now if this gets sorted by the new column and the region the customer with ID 10 comes before the customer with ID 9 because the one with ID 10 has the lower value for the new column -- 0 against the 1 from the customer with the ID 9.

Upvotes: 1

MSH
MSH

Reputation: 91

The idea is to use CASE statement to create a calculate virtual column to mark the nulls as 0 and none nulls as 1 and then sort accordingly.

if you use 0 in the order by clause you will get an error because you don't have a column at position of 0, also if you reorder the selected columns the result will be the same. so the output of case statement is not a position of column it's a calculated column.

customer_id        region      marker
not important      if null        0

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272086

so the query is sth like

ORDER BY 1, region

No this is incorrect. The expression CASE WHEN region IS NULL THEN 1 ELSE 0 END is evaluated per-row; and the 1 is a value instead of column position. Column position inside ORDER BY can only specified only as a literal and not as an expression. So this:

custid    region
8         NULL
9         NULL
10        BC
42        BC
45        CA

Becomes:

custid    region    case...
8         NULL      1
9         NULL      1
10        BC        0
42        BC        0
45        CA        0

And the sorted results could be:

custid    region    case...
10        BC        0
42        BC        0
45        CA        0
8         NULL      1
9         NULL      1

Or:

custid region case...
42     BC     0
10     BC     0
45     CA     0
9      NULL   1
8      NULL   1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Your interpretation is incorrect. The 1 is simple a number, not a column reference.

The query is equivalent to:

SELECT custid, region
FROM (SELECT c.*,
             (CASE WHEN region IS NULL THEN 1 ELSE 0 END) as region_is_null
      FROM Sales.Customers c
     ) c
ORDER BY region_is_null, region;

This is an important distinction about numbers in the ORDER BY. The expression:

ORDER BY 1

refers to the first column. However,

ORDER BY 1 + 0

is simply a numeric expression that returns the constant 1 -- and will result in an error in SQL Server (which does not allow constants in ORDER BY).

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try below - in your case 0 will be comign first then 1 so you need to change the order of the value, or you can do desc order if you don't want to change the value

SELECT custid, region
FROM Sales.Customers
ORDER BY
 CASE WHEN region IS NULL THEN 0 ELSE 1 END, region

Upvotes: 0

Related Questions