Fulfledge

Pull rows out, then filter them

INPUT · Slides

Just the first few

01 / 06

You do not need all of it

You wanted to know which one sold best, and all six rows came back. Reading that is work. With ten thousand rows it would be worse.

Deciding how many rows come back is LIMIT.

02 / 06

How LIMIT looks

Write LIMIT n at the very end of a query and you get that many rows.

Which rows depends on the order. If you have not said, you get them from the top in the order they went into the table.

SELECT name, price FROM booths  LIMIT 3;

Result

cotton candy | 150
grilled squid | 400
fruit juice | 120

03 / 06

Put it with ORDER BY

LIMIT really earns its keep next to ORDER BY. Sort first, take a few from the top, and you have a top three.

The writing order is WHERE, then ORDER BY, then LIMIT. LIMIT is always last.

SELECT name, sold FROM booths  ORDER BY sold DESC  LIMIT 3;

Result

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

04 / 06

Taking exactly one

LIMIT 1 hands back "the most whatever it is" as a single row. It is the standard way to ask for a best score or a lowest price.

Asking for more rows than exist is not an error either — you just get what there is. Let us write some.

SELECT name, price FROM booths  ORDER BY price DESC  LIMIT 1;

Result

grilled squid | 400

05 / 06

Flip the order to see the bottom

Want "the bottom few" instead of "the top few"? Do not touch LIMIT — flip the direction of ORDER BY. Swap DESC (biggest first) for ASC (smallest first) and the smallest comes out on top.

LIMIT always means "however many from the top". Which end "the top" points to is decided entirely by ORDER BY.

SELECT name, price FROM booths  ORDER BY price ASC  LIMIT 1;

Result

fruit juice | 120

06 / 06

OFFSET skips over the start

Add OFFSET n after LIMIT and it skips that many rows from the top before it starts counting. Handy for "I have already seen 1st through 3rd — show me the next three (4th through 6th)".

This is also the foundation of pagination — splitting a long list into "page 1, page 2" and so on.

SELECT name, sold FROM booths  ORDER BY sold DESC  LIMIT 3 OFFSET 3;

Result

cotton candy | 82
grilled squid | 65
crepes | 54