CODEWITHSUNDEEP
sqlsql-servert-sql
ExtremeSwat
ExtremeSwat

Reputation: 824

order-by case sorting in t-sql

I am unable to understand how the case functions correctly, I have a table called sales, this is how I interrogate it and this is a small fragment of the output, I am trying to do a challenge from a sql book

use TSQL2012
select custid,orderid, shippeddate
from sales.orders

This is the output ( small part of it)

85  10248   2006-07-16 00:00:00.000
79  10249   2006-07-10 00:00:00.000
34  10250   2006-07-12 00:00:00.000
80  11069   2008-05-06 00:00:00.000
44  11070   NULL
46  11071   NULL

So what I had to do is to get all the rows where the Customer ID ( custid ) is 20 AND to sort in an ascending order the shippeddate with the NULLs being placed at the end. This is what I've tried to do:

select orderid, shippeddate
from sales.orders
where custid = 20
order by 
    case when shippeddate is null then shippeddate end desc,
    case when shippeddate is not null then shippeddate end asc

It returns absolutely nothing, so after I cracked I decided to look on the internet for the solution, and this is it : 

use TSQL2012
select orderid, shippeddate
from sales.orders
where custid = 20
order by case
when shippeddate is null  then 1 else 0 end, shippeddate ;

What is 1 and 0 supposed to do ? They are not true/false, they are not ordinal positions, how can they replace desc respectively asc ? I am pretty new to SQL, sorry for the "explain me the code cos' i'm too dumb question"

Upvotes: 1

Views: 473

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

You can think about the construct case when shippeddate is null then 1 else 0 end as a derived column, which isn't returned to the client via select list, but is used for sorting.

You can look at it this way:

select orderid, shippeddate, case when shippeddate is null  then 1 else 0 end as [SortCol1]
from sales.orders
where custid = 20
order by SortCol1, shippeddate;

Upvotes: 3

Related Questions