CODEWITHSUNDEEP
mysqlsqlsql-serversql-order-by
vietstone
vietstone

Reputation: 9092

SQL order by with case

I meet a SQL statement with ORDER BY clause, that I can not understand.

SELECT ...
FROM ...
WHERE ...
JOIN ...
ORDER BY (
        CASE 
            WHEN versions.effective_date IS NULL THEN 
                1 
            ELSE 
                0 
        END) DESC, 
        versions.effective_date DESC, 
        versions.name DESC, 
        versions.id DESC

Please point me the meaning of ORDER BY clause. Thank you.

Upvotes: 2

Views: 141

Answers (5)

Paul Maxwell
Paul Maxwell

Reputation: 35583

It will place all rows where versions.effective_date IS NULL at the top of the list.

Basically it is dividing the result into 2 major sections

  • Top section NULL effective_date
  • Bottom Section NOT NULL effective_date

this case expression is similar to an IF() function in Excel

=IF(logical-test,value-if-true, value-if-false)

CASE 
    WHEN versions.effective_date IS NULL -- the logical test
    1 -- value if true
    else 0 -- value if false
END

Upvotes: 0

sameh.q
sameh.q

Reputation: 1709

Illustration:

Supposing original data is ordered like this

        effective_date, effective_date, name, id

            3/1/2010        3/1/2010     ABC   1
            1/1/2010        1/1/2010     ABC   2
            2/1/2010        2/1/2010     ABC   3
              NULL            NULL       ABC   4
              NULL            NULL       ABC   5
              NULL            NULL       ABC   6

After ordering will be

        effective_date, effective_date, name, id

              NULL            NULL       ABC   6
              NULL            NULL       ABC   5
              NULL            NULL       ABC   4
            3/1/2010        3/1/2010     ABC   1
            2/1/2010        2/1/2010     ABC   3
            1/1/2010        1/1/2010     ABC   2

Translation [how the order statement will be translated at run time]:

        effective_date, effective_date, name, id

                 1            NULL       ABC   6
                 1            NULL       ABC   5
                 1            NULL       ABC   4
                 0          3/1/2010     ABC   1
                 0          2/1/2010     ABC   3
                 0          1/1/2010     ABC   2

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Your results will be ordered in this way

  1. First show results when effective_date is null and the then show records who has effective_date
  2. Secondly if records have effective_date date then order them in descending way like records with greater date will be returned
  3. Thirdly within same criteria as above order records in alphabetical order but in descending way so the records whose name starts with z will be returned first
  4. And lastly within same criteria as above,order records with higher id first

Upvotes: 0

Tom Jonckheere
Tom Jonckheere

Reputation: 1648

The case clause is just an extra order by condition, every row where effective date is null has value 1, the others have value 0. The ordering happens first on that column, descending.

Upvotes: 0

Jesuraja
Jesuraja

Reputation: 3844

ORDER BY (CASE WHEN versions.effective_date IS NULL THEN 1 ELSE 0 END) DESC

If versions.effective_date not available (NULL), those records will be listed at last. All other records (NOT NULL) will be shown at top of the list.

Upvotes: 0

Related Questions