BOARDING PASS · What I Do

SQL ORDER BY Sorting and CASE WHEN for Price Bands

Sort SQL results with ORDER BY (ascending, descending, multiple columns), keep the top rows with LIMIT, and label price bands with CASE WHEN, plus where NULL sorts.

DATE

READ7 min read

GATESQL

SQL ORDER BY is the clause that sorts query results in the order you want, and CASE WHEN is an expression that changes a value depending on conditions, so you can attach band names like "10,000+ won." If last post's WHERE decided "which rows to see," today we decide in what order to show those rows and what names to group numbers under, and adding LIMIT lets you pull a top list like "the 3 largest orders" in a single query. When I started learning SQL, I assumed sorting was a one-click thing like in Excel, but there are parts where the result wobbles unless you spell them out yourself, such as the order among equal values or where NULL lands. In this post, let's follow along with ORDER BY, LIMIT and CASE WHEN one by one using a sample table. Queries are written in BigQuery (GoogleSQL) syntax. Previous post: SQL WHERE Clause: Filter Rows with Comparisons, AND/OR, IN, LIKE, NULL

SQL Basics series 8/12 · Full curriculum

Today's concept

ORDER BY does the same job as Excel's Sort feature. In an order sheet you can sort the amount column "largest to smallest" and then, when amounts are equal, sort once more by order number, so you can set several criteria. CASE WHEN is similar to nesting IF several times in Excel to write "if 10,000 won or more then A, otherwise if 5,000 won or more then B, otherwise C," and it's alike right down to checking conditions from the top and returning the first value that matches. There's one thing to know first. The BigQuery query syntax documentation says that if there's no ORDER BY, the order of the results isn't defined, which means you shouldn't expect rows to come out in the order they're stored. If order matters for a result, you always need to write ORDER BY yourself.

A diagram of SQL ORDER BY and LIMIT execution order where the order table goes through FROM, WHERE and SELECT, lines up by largest amount at ORDER BY, and only the top three rows remain at LIMIT 3 Concept diagram: ORDER BY lines rows up after SELECT, and LIMIT cuts from the top at the very end (figure labels in Korean)

Follow along: SQL ORDER BY and CASE WHEN

We'll use the same stationery shop order table shop.orders as last time (sample data). Keep in mind that there are two pairs of orders with the same amount (orders 1 and 2 at 6,000 won, orders 3 and 8 at 3,000 won).

orders

order_idorder_datechannelproductamountcoupon
12026-11-02Search adsPen6000NULL
22026-11-02SNSNotebook6000WELCOME
32026-11-03Search adsNotebook3000NULL
42026-11-03EmailPen15000VIP10
52026-11-04SNSNotebook set9000NULL
62026-11-05Search adsHighlighter10000WELCOME
72026-11-06SNSHighlighter set12000NULL
82026-11-06EmailNotebook3000NULL

1. Descending order and sorting by multiple columns: let's line rows up from the largest amount, and when amounts are equal, show the smaller order number first. According to the query syntax documentation, ASC is ascending, DESC is descending, and if you write nothing, ASC is the default.

SELECT order_id, channel, amount
FROM shop.orders
ORDER BY amount DESC, order_id;
order_idchannelamount
4Email15000
7SNS12000
6Search ads10000
5SNS9000
1Search ads6000
2SNS6000
3Search ads3000
8Email3000

Rows are first lined up by amount DESC, and only rows with the same amount, like orders 1 and 2 at 6,000 won and orders 3 and 8 at 3,000 won, are sorted again by the second criterion order_id. Here DESC applies only to amount right before it, and order_id uses the default ascending order; the PostgreSQL sorting documentation also explains that ordering options are applied to each column separately. Without a second criterion, the order among equal amounts isn't defined, so for a report where order matters, it's safer to add a non-repeating column like the order number at the end.

2. Seeing only the top few rows with LIMIT: if you only want the 3 largest orders, sort and then add LIMIT.

SELECT order_id, product, amount
FROM shop.orders
ORDER BY amount DESC
LIMIT 3;
order_idproductamount
4Pen15000
7Highlighter set12000
6Highlighter10000

According to the query syntax documentation, LIMIT is the upper bound on the number of rows returned, and with OFFSET it skips a number of rows from the front before cutting. The three amounts above don't overlap, so the result is fixed even without a second sort criterion, but if the 3rd and 4th amounts had been equal, which of the two rows got cut wouldn't have been defined. Other DBMSs use different syntax for cutting rows: according to the PostgreSQL SELECT documentation, LIMIT is syntax used by PostgreSQL and MySQL, while the SQL standard (SQL:2008) defines FETCH FIRST ... ROWS ONLY.

3. Splitting price bands with CASE WHEN: let's attach one of "10,000+ won," "5,000+ won" or "Under 5,000 won" to each order. CASE is written as CASE WHEN condition THEN value ... ELSE value END, and the result column name is attached with AS as in the last posts.

SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 10000 THEN '10,000+ won'
    WHEN amount >= 5000 THEN '5,000+ won'
    ELSE 'Under 5,000 won'
  END AS price_band
FROM shop.orders
ORDER BY order_id;
order_idamountprice_band
160005,000+ won
260005,000+ won
33000Under 5,000 won
41500010,000+ won
590005,000+ won
61000010,000+ won
71200010,000+ won
83000Under 5,000 won

According to the BigQuery conditional expressions documentation, CASE evaluates the WHEN conditions in turn and returns the THEN value of the first one that is TRUE, without looking at the remaining WHENs. So order 4 at 15,000 won also matches the second condition (5,000+ won), but it's already settled as "10,000+ won" at the first condition, and that's why you don't need to write amount < 10000 separately in the second WHEN. If every condition is FALSE or NULL, it returns the ELSE value, and if there's no ELSE, it returns NULL.

A diagram where orders of 15000, 9000 and 3000 pass through the three CASE WHEN condition gates from the top and get a 10,000+ won, 5,000+ won or Under 5,000 won label at the first gate that matches Concept diagram: CASE WHEN checks conditions from the top and settles the value at the first TRUE one (figure labels in Korean)

4. Setting your own sort order with CASE: what if you want to see channels in the order "Search ads → SNS → Email"? Writing just ORDER BY channel won't give that order. According to the BigQuery data types documentation, strings are compared by Unicode code point, so Email comes first, and SNS comes before Search ads because uppercase N has a smaller code point than lowercase e. In cases like this, you give each channel a rank number with CASE and sort by that number.

SELECT order_id, channel, amount
FROM shop.orders
ORDER BY
  CASE channel
    WHEN 'Search ads' THEN 1
    WHEN 'SNS' THEN 2
    ELSE 3
  END,
  amount DESC;
order_idchannelamount
6Search ads10000
1Search ads6000
3Search ads3000
7SNS12000
5SNS9000
2SNS6000
4Email15000
8Email3000

This time I used the form that writes a column right after CASE, like CASE channel WHEN 'Search ads' THEN 1, and the conditional expressions documentation describes it separately as "a CASE that compares the column value to each WHEN value in turn." Within the same channel, the second criterion amount DESC puts larger orders on top. All values in the result tables above were counted by hand, since the sample table is small.

Where does NULL go in ORDER BY?

If you sort by the coupon column (coupon), where do the five rows without a coupon, that is, the NULL rows, go? According to the data types documentation, in ORDER BY NULL is treated as the smallest value, so it comes first in ascending order and last in descending order. If you want to see orders that used a coupon first, add NULLS LAST, which is in the query syntax documentation; conversely, NULLS FIRST puts NULL at the top regardless of sort direction.

SELECT order_id, coupon
FROM shop.orders
ORDER BY coupon NULLS LAST, order_id;
order_idcoupon
4VIP10
2WELCOME
6WELCOME
1NULL
3NULL
5NULL
7NULL
8NULL

Without NULLS LAST, orders 1, 3, 5, 7 and 8 rise to the top and orders 4, 2 and 6 come below them. VIP10 comes before WELCOME because V has a smaller code point than W. It's also good to know that this default differs by DBMS. According to the PostgreSQL sorting documentation, PostgreSQL does the opposite and treats NULL as if it were the largest value, putting it last in ascending order, so if NULL positions look different when you run the same query on another database, I'd suggest thinking of this difference first. Either way, writing NULLS FIRST or NULLS LAST explicitly makes the results the same.

A diagram comparing that in ascending sort BigQuery puts NULL first, PostgreSQL puts it last, and with NULLS LAST both products put it last Concept diagram: with the ascending default, NULL position differs by product, and writing NULLS FIRST or LAST makes it the same (figure labels in Korean)

Here is the sorting and banding syntax so far in one table.

What you wantHow to write itGood to know
Smallest firstORDER BY amount or ASCASC is the default, NULL comes first
Largest firstORDER BY amount DESCNULL comes last
Multiple criteriaORDER BY amount DESC, order_idASC and DESC are set per column
Fix NULL positionNULLS FIRST · NULLS LASTOverrides each product's different default
Top N rowsORDER BY ... LIMIT 3Without ORDER BY, which rows come out isn't defined
Attach band namesCASE WHEN ... THEN ... ENDThe first TRUE value from the top
Sort in a custom orderORDER BY CASE channel WHEN ... ENDDefault string sorting is by code point

How is it used in practice? A list of top orders by amount

Continuing last post's weekly report query, let's imagine someone who pulls "last week's top 5 search ad and SNS orders by amount, along with price band and coupon usage." Everything we've learned so far, WHERE, CASE, ORDER BY and LIMIT, goes in.

  1. Filter rows with WHERE: search ad and SNS orders are the 6 rows 1, 2, 3, 5, 6 and 7.
  2. Build columns with CASE: attach the price band (price_band) and coupon usage (coupon_use). Coupons involve NULL, so use the form that writes the condition directly, like WHEN coupon IS NULL.
  3. Line up with ORDER BY: largest amount first, then order number when equal.
  4. Cut with LIMIT and check by hand: of the 6 rows, order 3 with the smallest amount (3,000 won) should drop out, leaving 5 rows.
SELECT
  order_id,
  channel,
  amount,
  CASE
    WHEN amount >= 10000 THEN '10,000+ won'
    WHEN amount >= 5000 THEN '5,000+ won'
    ELSE 'Under 5,000 won'
  END AS price_band,
  CASE WHEN coupon IS NULL THEN 'Not used' ELSE 'Used' END AS coupon_use
FROM shop.orders
WHERE channel IN ('Search ads', 'SNS')
ORDER BY amount DESC, order_id
LIMIT 5;
order_idchannelamountprice_bandcoupon_use
7SNS1200010,000+ wonNot used
6Search ads1000010,000+ wonUsed
5SNS90005,000+ wonNot used
1Search ads60005,000+ wonNot used
2SNS60005,000+ wonUsed

Orders 1 and 2, both at 6,000 won, made it into the top 5, so nothing was cut this time, but with LIMIT 4 the order_id criterion means it's settled that order 1 stays and order 2 drops. You can also use aliases set in SELECT in ORDER BY, because the query syntax documentation allows SELECT list aliases in ORDER BY. As we saw in the part 5 post on execution order, ORDER BY is evaluated after SELECT, so writing something like ORDER BY price_band is fine. That's the opposite of last post, where you couldn't use aliases in WHERE.

Practice problems

1. (Easy) From shop.orders, get the order number and date of the 2 most recent orders. If dates are equal, show the smaller order number first.

Show answer
SELECT order_id, order_date
FROM shop.orders
ORDER BY order_date DESC, order_id
LIMIT 2;

Orders 7 and 8, both from November 6, come out. The two orders have the same date, so the second criterion order_id puts order 7 first.

2. (Medium) Write a query that adds a channel_type column that says "Owned channel" if the channel is Email and "Ad channel" otherwise, and answer how many orders are "Owned channel."

Show answer
SELECT
  order_id,
  channel,
  CASE WHEN channel = 'Email' THEN 'Owned channel' ELSE 'Ad channel' END AS channel_type
FROM shop.orders
ORDER BY order_id;

The email orders 4 and 8, 2 orders, are "Owned channel," and the other 6 are "Ad channel." Writing it in the CASE channel WHEN 'Email' THEN ... form gives the same result.

3. (Medium) Show only orders that used a coupon, from the smallest amount, and attach the price band column (price_band) from query 3. Which order comes first?

Show answer
SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 10000 THEN '10,000+ won'
    WHEN amount >= 5000 THEN '5,000+ won'
    ELSE 'Under 5,000 won'
  END AS price_band
FROM shop.orders
WHERE coupon IS NOT NULL
ORDER BY amount;

The orders that used a coupon are 2, 4 and 6, and from the smallest amount they are order 2 (6,000 won, 5,000+ won), order 6 (10,000 won, 10,000+ won) and order 4 (15,000 won, 10,000+ won), so order 2 comes first.

Common mistakes

  • Using LIMIT without ORDER BY: SELECT ... LIMIT 3 returns "any 3 rows." The query syntax documentation says the order of rows returned by LIMIT and OFFSET isn't defined unless they're used after ORDER BY, so unless you're just previewing a few lines, always use ORDER BY together for "top N." If there may be ties, add a second criterion too.
  • Not writing CASE WHEN conditions from the narrowest: if you put WHEN amount >= 5000 above WHEN amount >= 10000, even the 15,000 won order becomes "5,000+ won" at the first condition, and not a single order lands in "10,000+ won." CASE stops at the first TRUE value, so when splitting bands by size, write them in order starting from the largest threshold.
  • Looking for empty values with CASE coupon WHEN NULL: the form that writes a column right after CASE compares like coupon = NULL, and according to the conditional expressions documentation, a comparison between NULLs becomes NULL and falls through to ELSE, and it advises using the other form to distinguish NULL. Find empty values with the form that writes the condition directly, like CASE WHEN coupon IS NULL THEN ....

Wrapping up

SQL ORDER BY sorts results, LIMIT cuts the sorted result from the top, and CASE WHEN attaches band names based on conditions. I'd suggest building the habits of adding one more sort criterion when there may be ties, writing NULLS LAST for columns with NULL, and writing CASE from the largest threshold. I'm studying along with the series myself, so please refer to the full curriculum. In the next post, I'll cover aggregate functions like COUNT and SUM, and how to build a per-channel totals table with GROUP BY and HAVING.

FAQ

What is the default sort direction of SQL ORDER BY?
Ascending (ASC). If you don't write a direction, as in ORDER BY amount, it sorts from the smallest value, and to see the largest first you add DESC, as in ORDER BY amount DESC. With multiple criteria, you write ASC or DESC separately for each column.
Can I use SQL LIMIT without ORDER BY?
There's no syntax error, but which rows come out isn't defined. The BigQuery documentation also explains that the order of rows returned by LIMIT is defined only when it's used after ORDER BY. When pulling the top few rows, write ORDER BY first and then add LIMIT.
What happens if I leave out ELSE in CASE WHEN?
Rows where no WHEN condition is TRUE get NULL. To make sure no row is left out when splitting bands, it's safer to set the remaining value with ELSE at the end.