Sorting, limiting and DISTINCT

Introduction

A table has no order of its own. Without ORDER BY, a DBMS may return rows in any order, and the order can change when the data or the DBMS version changes. If the order matters, say so.

A result is only useful when it comes in a sensible order and without repeats. These clauses turn a raw list of rows into a ranking, a page of results or a list of different values.

How it works

ORDER BY

• ORDER BY marks sorts from small to large (ASC, the default); ORDER BY marks DESC from large to small.
• ORDER BY dept, marks DESC sorts by dept first; rows with the same dept are then sorted by marks, largest first. The second column only breaks ties of the first.
• You can sort by a calculated column or its AS name.
• Text sorts alphabetically. NULL counts as the smallest value in SQLite (and MySQL), so it comes first in an ASC sort. PostgreSQL and Oracle put it last; write NULLS FIRST or NULLS LAST to be sure.

LIMIT and OFFSET

LIMIT 3 keeps only the first 3 rows of the result: with ORDER BY marks DESC, that is the top three. LIMIT 2 OFFSET 2 skips 2 rows and then keeps 2, which is how websites show results one page at a time. (SQL Server writes TOP 3; Oracle and the SQL standard write FETCH FIRST 3 ROWS ONLY.)

DISTINCT

SELECT DISTINCT city removes repeated rows from the result, so each city appears once. With several columns, DISTINCT removes rows where all of them repeat.

The order SQL works in

FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT

That is why ORDER BY may use a name made with AS in SELECT, while WHERE may not: when WHERE runs, that name does not exist yet.

Syntax

SELECT name, marks FROM students
ORDER BY marks DESC, name
LIMIT 3 OFFSET 0;

SELECT DISTINCT city FROM students ORDER BY city;

Example

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  city TEXT,
  marks INTEGER
);
INSERT INTO students (name, dept, city, marks) VALUES
  ('Rahim', 'CSE', 'Dhaka', 85),
  ('Karim', 'EEE', 'Sylhet', 72),
  ('Nadia', 'CSE', 'Dhaka', 91),
  ('Tania', 'BBA', 'Khulna', 64),
  ('Sumon', 'CSE', 'Rajshahi', 85),
  ('Rina', 'EEE', 'Dhaka', 79);

-- Ties on marks are broken by name
SELECT name, marks FROM students ORDER BY marks DESC, name;

-- The top three
SELECT name, marks FROM students ORDER BY marks DESC LIMIT 3;

-- Page 2, with 2 rows per page
SELECT name FROM students ORDER BY name LIMIT 2 OFFSET 2;

SELECT DISTINCT city FROM students ORDER BY city;

The example sorts six students in four different ways.

Line by line

1. ORDER BY marks DESC, name puts the highest marks first. Rahim and Sumon both have 85, so name decides between them.
2. ORDER BY marks DESC LIMIT 3 sorts first and then keeps three rows: the top three.
3. ORDER BY name LIMIT 2 OFFSET 2 skips the first two names alphabetically and keeps the next two, which is page 2 at two rows per page.
4. SELECT DISTINCT city ... ORDER BY city lists each city once. Dhaka appears in three rows but once in the result.

Expected result

name   marks
-----  -----
Nadia  91
Rahim  85
Sumon  85
Rina   79
Karim  72
Tania  64
(6 rows)

name   marks
-----  -----
Nadia  91
Rahim  85
Sumon  85
(3 rows)

name
-----
Rahim
Rina
(2 rows)

city
--------
Dhaka
Khulna
Rajshahi
Sylhet
(4 rows)

In the real world

Every leaderboard, "most recent first" list and page of search results is an ORDER BY with a LIMIT. Online shops page through products with LIMIT and OFFSET, or with faster methods based on the last row shown.

Common mistakes

• Using LIMIT to get "the top" rows without an ORDER BY.
• Expecting ORDER BY a, b DESC to sort a from largest to smallest: DESC only applies to b.
• Using DISTINCT to hide duplicate rows caused by a wrong join, instead of fixing the join.

Best practices

• Add a final, unique sort column, such as the id or name, so that ties always come out the same way.
• Say NULLS FIRST or NULLS LAST when the position of NULL matters.
• Use DISTINCT only when repeated rows are really possible and unwanted.

Summary

ORDER BY sorts a result by one or more columns, LIMIT and OFFSET keep a slice of it, and DISTINCT removes repeated rows. SQL applies them after WHERE and SELECT, which is why ORDER BY can use names made with AS.

Mark lesson complete