ConceptDBISAI-written · not yet proofread

Ordering

Also ORDER BY

Ordering (ORDER BY) turns the unordered multiset of result rows into a sequence sorted by one or more expressions, ascending or descending, on which LIMIT and OFFSET select a segment.

Know these firstRelational Model SQL Query Structure

Definition

Tables are multisets of rows and have no inherent order (Relational Model). For the final result of a query, an order is requested with

ORDER BY e_1 [ASC|DESC], ..., e_m [ASC|DESC]

at the end of the SELECT query, after any GROUP BY. Formally, for a table $T = (C, D, R)$ with $|R| = n$ , the result is the sequence $(r_{\pi(1)}, \ldots, r_{\pi(n)})$ of all rows of $R$ (with multiplicity), for a permutation $\pi$ such that consecutive rows are in lexicographic order of the sort keys $(e_1(r), \ldots, e_m(r))$ , each key compared ascending (ASC, the default) or descending (DESC). Here $e_i$ is an expression over the columns (usually a column name, possibly an alias of an aggregate).

Given an order, LIMIT $l$ keeps the first $l$ rows of the sequence and OFFSET $o$ first skips $o$ rows: LIMIT l OFFSET o returns $(r_{\pi(o+1)}, \ldots, r_{\pi(\min(o+l,\,n))})$ .

Properties#

Clause order: GROUP BY → ORDER BY → LIMIT / OFFSET; in the logical evaluation order, ordering comes after SELECT (so it may use result aliases such as total_views) and before LIMIT (SQL Query Structure).
Result is a sequence, not a table: the output of an ordering is no longer a multiset. That is why it belongs at the end of a query; without ORDER BY the row order of a result is unspecified, and “the order of rows is not specified” is a valid remark in exam solutions.
Ties: rows with equal sort keys may come in any order; add further keys to make the order unique.
Top- $k$ queries: ORDER BY … DESC LIMIT k gives the $k$ rows with the largest key; per-group maxima need Grouping instead.
SPARQL: the same clauses exist, but the direction is written like a function of the key: ORDER BY DESC(?date) instead of SQL’s ORDER BY date DESC (DBIS_VL07 > PREFIX, FILTER, ORDER BY, LIMIT).

Example#

ExampleSort, top 10, and a segment

movies(title, year, score):

SELECT * FROM movies ORDER BY score DESC;                         -- best first

SELECT year, MAX(score) AS max_score FROM movies
GROUP BY year ORDER BY year ASC;                                  -- after GROUP BY

SELECT title, score FROM movies ORDER BY score DESC LIMIT 10;      -- top 10

SELECT title, score FROM movies
ORDER BY score DESC LIMIT 5 OFFSET 10;                            -- ranks 11–15

For the rows (Oppenheimer, 2023, 8.5), (Kashmir files, 2022, 8.6), (Western Front, 2022, 7.8), (The Whale, 2022, 7.7), (Dune, 2021, 8.0), (King Richard, 2021, 7.5), ORDER BY score DESC LIMIT 2 returns (Kashmir files, 8.6), (Oppenheimer, 8.5).

Sources#

Slides 05, p. 31 (ORDER BY), p. 32 (LIMIT and OFFSET)
Slides 03, p. 10 (rows have no order)
DBIS_VL05 > ORDER BY, DBIS_VL05 > LIMIT and OFFSET, DBIS_VL03 > Sets vs. multisets
Exams: DBIS Exam WS2324 (not published) (Tasks 2.1, 3.3), DBIS Exam WS2324-2 (not published) (Task 3.3)

Connections

Mentioned in inline links8

Relational Algebra
| ORDER BY, LIMIT | Ordering (by example only, VL05) | §
Relational Model
Neither sets nor multisets have an order: a database need not remember insertion order and may reorder rows to work more efficiently. An order of the final result is requested with ORDER BY (see Ordering); a persistent order needs an expli… §
SQL Query Structure
| ORDER BY | 6 | sorts the final result | Ordering | §
| LIMIT/OFFSET | 7 | keeps a segment of the sorted result | Ordering | §
VL05 – Joins and more SQL
Ordering — ORDER BY, with LIMIT and OFFSET selecting a segment of the sorted rows §
SQL and Query Planning
Ordering — ORDER BY, LIMIT, OFFSET §
SPARQL AggregationADIS
The aggregate is a new variable and can be used afterwards, e.g. in ORDER BY DESC(?num_papers) (Ordering). §
VL01 – Overview, RDF & SPARQL, more SPARQLADIS
ORDER BY DESC(?population) LIMIT 10 gives the ten most populous German cities (Ordering) (Slide 20). §
Ordering — ORDER BY DESC(…) and LIMIT (DBIS) §
Advanced SPARQLADIS
Basics from DBIS, used throughout: RDF Triple, SPARQL Query Structure (patterns, FILTER, LANG), Wikidata Prefix System, Ordering (ORDER BY, LIMIT). §
| ORDER BY / LIMIT | sorted, then truncated | — | same (Ordering) | §

Search in these mentions →