Case WHEN statement for ORDER BY clause

Constructing multi-conditional sorting

Take your CASE WHEN ordering up a notch by handling more intricate sorting scenarios, such as sorting orders by status and within each status by the order date:

SELECT orderId, orderStatus, orderDate
FROM orders
ORDER BY 
  CASE orderStatus
    WHEN 'Pending' THEN 1       -- Pending: Just like my diet plans
    WHEN 'Processing' THEN 2    -- Processing: Good things come to those who wait
    WHEN 'Complete' THEN 3      -- Complete: The 'done and dusted' squad
    ELSE 4                      -- Everyone else: The wild cards
  END,
  orderDate ASC;


Quick tip: Sequencing of conditions is vital, as it determines the final order - place higher priority conditions at the top.

Ascending and descending combo

Harness the power of ASC and DESC within your CASE WHEN for a glorious blend of orderly chaos:

SELECT productId, productName, productCategory, productPrice
FROM products
ORDER BY 
  CASE productCategory
    WHEN 'Smartphones' THEN 1   -- Smartphones: Can't live without them, can't live without them...
    ELSE 2                      -- The rest: Still cooler than a flip phone 
  END,
  CASE WHEN productCategory = 'Smartphones' THEN productPrice END DESC,
  productName ASC;


This puts 'Smartphones' first, sorted by price descending, followed by other categories sorted by product names ascending.

NULLs and custom order

When dealing with NULLs, you can determine their position during sorting:

SELECT clientId, clientName, lastContactDate
FROM clients
ORDER BY 
  CASE 
    WHEN lastContactDate IS NULL THEN 0  -- NULLs: The mystery guests
    ELSE 1                                -- Rest: The know-it-alls
  END,
  lastContactDate DESC;


By assigning a lower value to NULLs, they debut first. Swap the values for an encore.

Conductor of complex scenarios

For more advanced scenarios, where multiple layers of priority need to be set, think like the music conductor again!

SELECT customerName, dealValue, customerType
FROM deals
ORDER BY
  CASE customerType
    WHEN 'VIP' THEN 1            -- VIPs: Tuxedos and spotlights
    WHEN 'Preferred' THEN 2      -- Preferred: No one likes being second, but it's better than third!
    ELSE 3                       -- The crowd: Where the real party's at
  END,
  CASE 
    WHEN customerType IN ('VIP', 'Preferred') THEN dealValue
    ELSE NULL
  END DESC,
  customerName ASC;


Conducting without faux-pas

A few words of caution while brandishing your CASE WHEN baton:

• Syntax errors: Getting "Incorrect syntax near 'desc'"? Make sure DESC is chilling outside the CASE.
• Performance: Especially for large datasets, always put your queries through a performance test. Complex case statements can sometimes take the tempo down a notch.
• Readability: Don't let the music sheet (your SQL) turn into a spiderweb. Keep your formatting and commenting game strong.

Testing under the spotlight

Running through the rehearsals (test your queries) before the final performance can save a lot of encore embarrassment:

• Watch out for multiple rows grabbing the top spot.
• Handle the ambiguous ones who don't win any favor (all conditions evaluate to ELSE).
• And remember, everyone wants to be unique; handle those tie situations by preparation and a secondary sort order.