Datawise
Practical SQL

ORDER BY expressions in SQL

Constantin LunguUpdated 1 min read

Photo by Héctor J. Rivas on Unsplash

Friendly reminder: when you ORDER BY something in SQL, that something does not necessarily need to be a column, but could be an expression, the output of which can be ordered.

In the example below, we'd like to ORDER by sales decreasingly, but show the 'direct' sales first.

This is achieved by using a CASE WHEN that will rank direct sales above other types of sales, then sorting by the sales decreasingly.

WITH input_data AS (
  SELECT 'US' AS country, 100 AS sales, 'direct' AS channel
  UNION ALL
  SELECT 'US' AS country, 125 AS sales, 'partners' AS channel
  UNION ALL
  SELECT 'FR' AS country, 170 AS sales, 'direct' AS channel
  UNION ALL
  SELECT 'FR' AS country, 200 AS sales, 'partners' AS channel
  UNION ALL
  SELECT 'IT' AS country, 150 AS sales, 'direct' AS channel
  UNION ALL
  SELECT 'IT' AS country, 100 AS sales, 'partners' AS channel
)

SELECT
  country,
  sales,
  channel
FROM input_data

ORDER BY CASE WHEN channel = 'direct' THEN 1 ELSE 0 END DESC, sales DESC

BigQuery results: the direct rows first (FR 170, IT 150, US 100), then the partners rows (FR 200, US 125, IT 100).

Enjoyed this? Here are some related articles you might find useful: