Sorting & Ordering
8 Functions
Sorting and ordering are fundamental to data retrieval. In SQL, the order of result rows is never guaranteed unless you explicitly define it. These tools allow you to organize data logically—whether you are displaying the top players in a league or paginating through a long list of quests.
ORDER BY
The primary clause used to sort the result set of a query.
SELECT col FROM table ORDER BY col_name;Displaying the Leaderboard sorted by user XP.
SELECT username, xp FROM users ORDER BY xp;You can sort by multiple columns. For example, ORDER BY xp DESC, username ASC will sort by XP first, and then alphabetically for users who have the exact same XP.
ASC (Ascending)
Sorts the data in ascending order (smallest to largest, A to Z).
ORDER BY col_name ASC;Sorting a list of Quests by their difficulty level or creation date (oldest first).
SELECT * FROM quests ORDER BY difficulty ASC;ASC is the default sorting order in PostgreSQL. You don't technically need to type it, but doing so makes your code more readable for others.
DESC (Descending)
Sorts the data in descending order (largest to smallest, Z to A).
ORDER BY col_name DESC;The standard for any Leaderboard—showing the player with the highest XP at the very top.
SELECT * FROM leaderboard ORDER BY xp DESC;When sorting by dates, DESC shows the most recent (newest) items first.
NULLS FIRST
A modifier that forces rows with NULL values to appear at the beginning of the sorted list.
ORDER BY col_name DESC NULLS FIRST;Finding users who have never logged in (where last_login is NULL) by putting them at the top of an audit list.
SELECT username, last_login FROM users ORDER BY last_login DESC NULLS FIRST;By default, PostgreSQL considers NULL values to be "larger" than any non-null value, so they naturally go to the end in ASC and the beginning in DESC. Use this keyword to override that behavior.
NULLS LAST
A modifier that forces rows with NULL values to appear at the end of the sorted list.
ORDER BY col_name ASC NULLS LAST;Sorting players by their "Arena Win Rate" but keeping those who haven't played any matches (NULL) at the bottom.
SELECT username, win_rate FROM users ORDER BY win_rate ASC NULLS LAST;This is vital for professional UI/UX, as users usually don't want to see "Empty" data at the top of their search results.
LIMIT
Restricts the total number of rows returned by the query.
SELECT * FROM table LIMIT 10;Showing only the Top 5 players on the dashboard widget instead of the entire league.
SELECT * FROM users ORDER BY xp DESC LIMIT 5;Always use LIMIT in combination with ORDER BY. Without an order, LIMIT will just give you a random set of rows.
OFFSET
Skips a specified number of rows before starting to return results.
SELECT * FROM table LIMIT 10 OFFSET 20;Implementing Pagination. To see "Page 3" of the Quest list (assuming 10 per page), you skip the first 20 rows.
SELECT * FROM quests ORDER BY id LIMIT 10 OFFSET 20;The formula for pagination is usually: OFFSET = (page_number - 1) * items_per_page.
FETCH NEXT
The SQL Standard alternative to LIMIT, used to retrieve a specific number of rows.
SELECT * FROM table FETCH NEXT 5 ROWS ONLY;Writing queries that need to be compatible with other databases like Oracle or SQL Server.
SELECT * FROM users ORDER BY id FETCH NEXT 5 ROWS ONLY;While LIMIT is more common in the PostgreSQL and MySQL communities, FETCH NEXT is the "official" way according to the SQL standard.