ORDER BY

So far, we haven't paid much attention to the order of the rows from top to bottom. If we care about the order, we use the ORDER BY clause.

SELECT *
FROM daters
ORDER BY height_cm;

`name` `VARCHAR`	`height_cm` `INTEGER`	`eye_color` `VARCHAR`
Claude	156	brown
Florence	167	blue
Frederick	170	green
Kim	178	blue
Ray	180	brown
Anita	188	brown

If you want to reverse the order, you can add DESC, which stands for "descending", to the end of the clause.

SELECT *
FROM daters
ORDER BY height_cm DESC;

`name` `VARCHAR`	`height_cm` `INTEGER`	`eye_color` `VARCHAR`
Anita	188	brown
Ray	180	brown
Kim	178	blue
Frederick	170	green
Florence	167	blue
Claude	156	brown

Multiple-Column Sorting

What if 2 rows have the same value in the ordered column? How do we know which row will be first? We cannot know. It is up to the database system.

SELECT *
FROM cars
WHERE asking_price_usd BETWEEN 5000 AND 7000
ORDER BY asking_price_usd;

`id` `INTEGER`	`make` `VARCHAR`	`model` `VARCHAR`	`year` `INTEGER`	`color` `VARCHAR`	`asking_price_usd` `INTEGER`	`miles_driven` `NUMERIC`	`is_paid_off` `BOOL`
90175	hyundai	elantra	2012	gray	5600	112000.0	TRUE
92127	nissan	sentra	2016	black	6000	110000.0	TRUE
50726	chevrolet	cruze	2014	blue	6000	102000.0	TRUE
20546	honda	accord	2008	blue	6000	141000.0	TRUE
48069	hyundai	tucson	2010	blue	6000	124749.8	TRUE
84470	honda	civic	2006	white	6250	6000.0	TRUE
31239	honda	civic	2007	black	6500	134000.0	TRUE
38935	honda	civic	2014	black	6900	80716.6	FALSE
77029	lexus	es	2003	silver	6950	84591.3	TRUE

We can actually choose multiple columns to sort on.

SELECT *
FROM cars
WHERE asking_price_usd BETWEEN 5000 AND 7000
ORDER BY asking_price_usd, miles_driven;

`id` `INTEGER`	`make` `VARCHAR`	`model` `VARCHAR`	`year` `INTEGER`	`color` `VARCHAR`	`asking_price_usd` `INTEGER`	`miles_driven` `NUMERIC`	`is_paid_off` `BOOL`
90175	hyundai	elantra	2012	gray	5600	112000.0	TRUE
50726	chevrolet	cruze	2014	blue	6000	102000.0	TRUE
92127	nissan	sentra	2016	black	6000	110000.0	TRUE
48069	hyundai	tucson	2010	blue	6000	124749.8	TRUE
20546	honda	accord	2008	blue	6000	141000.0	TRUE
84470	honda	civic	2006	white	6250	6000.0	TRUE
31239	honda	civic	2007	black	6500	134000.0	TRUE
38935	honda	civic	2014	black	6900	80716.6	FALSE
77029	lexus	es	2003	silver	6950	84591.3	TRUE

This will order by asking_price_usd first. When multiple rows have the same asking_price_usd, it will then order by which has the lowest miles_driven.

Order Non-Determinism

If you don't have an ORDER BY clause at all, then nothing can be guaranteed about the order of the result. In fact, running the same exact query on the same data might have differently-ordered results.