Fulfledge

Pull rows out, then filter them

INPUT · Slides

Put it in the order you want

01 / 05

The order it comes in is not the order you want

Everything so far came back in the order it went into the table. But what you want to know is "best selling first", or "cheapest first".

You get to say what order the result comes in. That is ORDER BY.

02 / 05

How ORDER BY looks

Add ORDER BY column at the end of the query and it comes back smallest first on that column. With numbers, the small ones lead.

It goes after FROM and WHERE, right at the very end. That is the rule.

SELECT name, price FROM booths  ORDER BY price;

Result

fruit juice | 120
cotton candy | 150
shaved ice | 200
crepes | 280
grilled noodles | 350
grilled squid | 400

03 / 05

Biggest first is DESC

Add DESC after the column name and it goes biggest first. That is the shape of every ranking.

Smallest first can be written ASC, but since that is what you get anyway, people usually leave it off.

SELECT name, sold FROM booths  ORDER BY sold DESC;

Result

fruit juice | 140
shaved ice | 118
grilled noodles | 97
cotton candy | 82
grilled squid | 65
crepes | 54

04 / 05

It goes after WHERE

Narrowing and sorting work together. The order you write them in is fixed: WHERE then ORDER BY.

The other way round will not be read at all, so get this order into your fingers.

SELECT name, sold FROM booths  WHERE sold >= 90  ORDER BY sold DESC;

Result

fruit juice | 140
shaved ice | 118
grilled noodles | 97

05 / 05

Text sorts too

ORDER BY works on a text column as well. What it sorts by is the character code, though. With plain English text that comes out alphabetical, but a capital letter sorts before every lowercase one.

When the order is not what you expected, start by looking at the column you sorted on. Let us write some.

SELECT name FROM booths  ORDER BY name;

Result

cotton candy
crepes
fruit juice
grilled noodles
grilled squid
shaved ice