The most important thing in the programming language is the name. A language will not succeed without a good name. I have recently invented a very good name and now I am looking for a suitable language.
—Donald Knuth

The LIMIT clause

In this section, we'll look at how to limit the number of result rows being shown. Coincedentally, this little topic provides a good occasion to talk a little about (SQL) standards.

LIMIT

Often, as a result of a query, you only want to see the first 3, 5, 10, ... results. The following query shows the top two courses with the largest number of credits:

SELECT code, name, credits
FROM course
ORDER BY credits DESC
LIMIT 2;

It makes sense that you can only do this meaningfully if there is also an ORDER BY clause present. Here's the result:

OFFSET

Suppose you don't want to see numbers and 1 and 2 as above, but you do want to see the two next (i.e., numbers 3 and 4 in the sorted list). This can be done easily as follows:

SELECT code, name, credits
FROM course
ORDER BY credits DESC
LIMIT 2 OFFSET 2;

Standard SQL

You might make the observation in the previous query that more courses have 6 credits. The system – as requested – effectively shows only two. We can imagine situations where we would like to see the other courses of 6 credits (the ‘ex aequos’ we often call them).

By the way, there is something else interesting: LIMIT and OFFSET are not actually standard SQL. For a long time there was a need to limit the number of rows to be limited to the top so many rows. The SQL standard had no solution for this and so every database creator started working on their own version. A number of DBMS (including MySQL, PostgreSQL, ...) came up with the LIMIT syntax. It wasn't until 2008 that a way came along in standard SQL to do this: FETCH FIRST ... ROWS (ONLY or WITH TIES). The previous query according to standard SQL could look like this (also supported by PostgreSQL):

SELECT code, name, credits
FROM course
ORDER BY credits DESC
OFFSET 2
FETCH FIRST 2 ROWS ONLY;

The result is identical to the LIMIT / OFFSET query: results 3 and 4 are displayed from the sorted list (decreasing number of credits).

Moreover, this syntax has an elegant solution to the problem of ‘ex aequos’: WITH TIES. Next query shows the numbers 3 and 4 of the list, unless there are still courses with the same number of credits as number 4. In that case, the list continues, as the figure below the code shows:

SELECT code, name, credits
FROM course
ORDER BY credits DESC
OFFSET 2
FETCH FIRST 2 ROWS WITH TIES;

Exercises

SELECT coordinator, SUM(credits) AS number_of_credits_coordinated
FROM course
GROUP BY coordinator
ORDER BY SUM(credits) DESC
OFFSET 3
FETCH FIRST 1 ROW WITH TIES;