Sort and Limit
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 4 of 20.
Tables have no guaranteed display order. ORDER BY sorts the result; ASC means smallest first and DESC means largest first. Add a unique id as a second sort key when values tie.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, price FROM products ORDER BY price DESC, id ASC LIMIT 2;The example returns the two highest prices: id 5 at 30 and id 3 at 24. Sorting happens before LIMIT selects the first two rows. The id tie-breaker makes equal prices predictable.
Use ORDER BY for reliable order and LIMIT to cap the result after sorting.
Challenge
EasyReturn id, name and stock for the three products with the least stock. Sort by stock ascending, then id ascending.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
Practice on your own: PostgreSQL playground