SQL ORDER BY

ORDER BY sorts the rows of a result, from smallest to largest or the other way round.

ORDER BY Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC|DESC;

• ASC means ascending: A to Z, 1 to 100. It is the default, so you can leave it out.
• DESC means descending: Z to A, 100 to 1.

Without ORDER BY, the database may return rows in any order. If the order matters, always ask for it.

Sort Ascending

SELECT name, age
FROM students
ORDER BY age;

Sort Descending

To see the highest marks first, add DESC:

SELECT name, marks
FROM students
ORDER BY marks DESC;

Sort Text Alphabetically

Text columns sort in alphabetical order:

SELECT title
FROM courses
ORDER BY title;

Sort by Several Columns

You can give more than one column. The database sorts by the first column, and when two rows have the same value it uses the second column.

SELECT name, city, age
FROM students
ORDER BY city, age DESC;

Cities are A to Z. Inside each city, the oldest student comes first.

ORDER BY with WHERE

WHERE comes before ORDER BY. First the rows are filtered, then sorted:

SELECT name, marks
FROM students
WHERE city = 'Karachi'
ORDER BY marks DESC;

Show Only the Top Rows

To get only the first few rows, MySQL, PostgreSQL and SQLite use LIMIT. Here are the top 3 students:

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

Order of the Clauses

Exercise