SQL Café · Lesson 2 of 8

Please line them up, and just bring me three.

Last time: SELECT What? FROM Where is it? WHERE Which ones? Lesson 1

Today you learn two new words. One puts your answer in order. The other says how many rows you want. With them you can ask for things like “the 3 cheapest drinks”.

You say to the waiter “Please bring me the item and the price from the menu, only drinks, cheapest first, and just 3 of them. Thank you!”
↓ same thing in SQL
SELECT item, price FROM menu WHERE type = 'drink' ORDER BY price LIMIT 3;
Two new questions

In what order? How many?

Lesson 1 gave you three questions. Today adds two more, each with its own colour.

ORDER BY In what order? Lines the rows up, like people in a queue

Name the column to sort by, like ORDER BY price. Without it, the order of the rows is not guaranteed.

LIMIT How many? Keeps only the first few rows

Give a number, like LIMIT 3. You get the first 3 rows and the rest are left behind.

Up or down?

ASC walks up the stairs, DESC walks down

After the column name you can add one small word to choose the direction. Think of a staircase.

ASC Ascending = going up. Small to big. A to Z. ORDER BY price ASC → £2.50, £3.00, £3.50 …
DESC Descending = going down. Big to small. Z to A. ORDER BY price DESC → £5.50, £4.50, £4.00 …
Good to know: if you write nothing, SQL walks up. ORDER BY price is the same as ORDER BY price ASC. So you only need to type DESC when you want biggest first.
Sort first, then cut: imagine lining children up from shortest to tallest, then picking the first 3. You get the 3 shortest. That is why ORDER BY always comes before LIMIT. If you cut first, you would get any 3.
The full order, in one sentence

Some Friendly Waiters Offer Lemonade

Now there are five words, and they always go in the same order. The first letter of each word in this sentence gives you the order.

SomeFriendlyWaitersOfferLemonade
SELECT→FROM→WHERE→ORDER BY→LIMIT

You can skip WHERE, ORDER BY or LIMIT when you don't need them. But the ones you use always stay in this order.

Read the code one word at a time

Every piece has a meaning

Please press Next (or click any piece) and watch the sentence grow underneath. The new pieces from today are in teal and green.

Say it out loud
Today's words and symbols

Say each one out loud

Swap each word for what you say, and the code becomes a normal sentence again.

WriteSay it asExampleWhy it's there
Putting rows in order
ORDER BY“sorted by…”ORDER BY priceTwo words that always go together. Says which column decides the order.
ASC“smallest first”ORDER BY price ASCUp the stairs: small to big, A to Z. This is what you get if you write nothing.
DESC“biggest first”ORDER BY price DESCDown the stairs: big to small, Z to A.
,“then by…”ORDER BY type, priceSort by type first. When two rows have the same type, sort those by price.
Choosing how many
LIMIT“only the first…”LIMIT 3Always the very last word. Keeps that many rows from the top.
3“three”LIMIT 3A whole number with no quotes, because it is a number, not text.
The comma changes its job depending on where it is. After SELECT it means “and”: SELECT item, price shows item and price. After ORDER BY it means “then by”: ORDER BY type, price sorts by type, then by price when the types are the same.
How text is sorted: like a dictionary, A to Z. Numbers are sorted by size, so 9 comes before 10.
Your turn

Playground: watch the rows line up

Please type a query and press Run. Watch the rows slide into order, then see LIMIT keep only the first few.

Mission

Ctrl + Enter also runs
Or try one of these
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

The database works in this order: FROM, WHERE, SELECT, ORDER BY, LIMIT. Faded columns stay until the end, so you can still sort by them.

Lock it in

Five quick questions

Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

Take this with you

Your pocket card

Please take a screenshot of this card. Read it once tomorrow and once next week.

SELECTWhat do you want to see?
FROMWhere is it?
WHEREWhich ones?
ORDER BYIn what order?
LIMITHow many?

  • Order to write them: Some Friendly Waiters Offer Lemonade.
  • ASC walks up (small to big, A to Z). It is the default.
  • DESC walks down (big to small, Z to A).
  • Sort first, then cut: ORDER BY before LIMIT.
  • In ORDER BY, the comma means “then by”.

SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.