How does ORDER BY decide the sort order, and what happens when two rows have the same sort value?
It sorts the rows of the answer, and it is the last step before the rows are handed back.
The syntax is simple. The word ORDER BY, then the column or the expression to sort by, then optionally the direction. The direction is ASC for going up, which is what you get if you leave it out, and DESC for going down. You can sort by more than one column, and they are applied in the order that you write them.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
category VARCHAR(60) NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (id, name, category, unit_price) VALUES
(1, 'Notebook', 'Stationery', 4.50),
(2, 'Desk lamp', 'Furniture', 24.00),
(3, 'Pen', 'Stationery', 1.20),
(4, 'Chair', 'Furniture', 89.00);
-- Sort by two columns. The first one is the main sort.
SELECT id, name, category, unit_price
FROM products
ORDER BY category ASC, unit_price DESC;
So what happens when two rows have the same sort value? The order between them is not decided. The engine is free to return them in any order it likes, and it may well give you a different order the next time you run the same query.
That is not a small detail, because it breaks paging. If you ask for the first ten rows and two of them have the same sort value, then the engine might put one of them on the first page and the other on the second, or it might not. So the fix is to add a column that is never equal, and that is almost always the primary key.
-- A steady order. The id breaks the tie.
SELECT id, name, category, unit_price
FROM products
ORDER BY category ASC, unit_price DESC, id ASC;
So the rule to remember is this. Always end the order by with something that is unique, so that the answer comes back the same way every time you ask for it.
The next question in this topic looks at how text is sorted.