SQL · Theory

ORDER BY

Theory

43/439

ORDER BY

ORDER BY marks DESC; sorts the result. ASC is default (low to high). ORDER BY city, name; sorts by city, then name inside the same city.

ORDER BY 2 means second selected column — don’t do that in interviews; name the column. NULLs sort first or last depending on engine/settings — don’t assume.

LIMIT 5 after ORDER BY = top 5. Without ORDER BY, LIMIT is ‘some 5 rows’, not ‘best 5’.

Trap — ORDER BY RAND() on a huge table. Trap: sorting in PHP after SELECT *.

On the example next to this theory — ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Exam tip

ORDER BY marks DESC LIMIT 5; — top 5 marks.

ORDER BY — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefORDER BY sorts the result. DESC = high to low.
RuleName columns, not positions. LIMIT after ORDER BY for top-N.
TrapLIMIT without ORDER BY is not ‘top N’.

SQL · Theory

ORDER BY

← All stacks

Theory

43/439

ORDER BY

ORDER BY marks DESC; sorts the result. ASC is default (low to high). ORDER BY city, name; sorts by city, then name inside the same city.

ORDER BY 2 means second selected column — don’t do that in interviews; name the column. NULLs sort first or last depending on engine/settings — don’t assume.

LIMIT 5 after ORDER BY = top 5. Without ORDER BY, LIMIT is ‘some 5 rows’, not ‘best 5’.

Trap — ORDER BY RAND() on a huge table. Trap: sorting in PHP after SELECT *.

On the example next to this theory — ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Exam tip

ORDER BY marks DESC LIMIT 5; — top 5 marks.

ORDER BY — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

ORDER BY — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefORDER BY sorts the result. DESC = high to low.
RuleName columns, not positions. LIMIT after ORDER BY for top-N.
TrapLIMIT without ORDER BY is not ‘top N’.

ORDER BY

ORDER BY — sample query

Short notes

Questions

ORDER BY

ORDER BY — sample query

Short notes

Questions