Sorting, LIMIT and DISTINCT

Lesson 4 of 21 · Part 1 · Querying data · about 15 minutes

So far, the database has decided the order of the rows in every result. It looks like they come by id, but nothing promises that: in another database, or with more data, the order can change. When you want a fixed order, add ORDER BY:

1SELECT name, price2FROM products3ORDER BY price;

ORDER BY

ORDER BY column sorts by that column from small to large. It goes after WHERE. Numbers compare by size, dates by earlier and later. Customers sorted by the date they joined, earliest first:

1SELECT name, joined2FROM customers3ORDER BY joined;

Small to large is ascending, written ASC. It's what you get when you write nothing, so ORDER BY joined ASC is the same as the query above.

Large to small: DESC

Put DESC after the column name for descending order, large to small. The newest orders first:

1SELECT id, order_date, status2FROM orders3WHERE status = 'shipped'4ORDER BY order_date DESC;

This query first picks the shipped orders with WHERE, then sorts them. The column you sort by doesn't have to be selected either: ORDER BY stock works fine while showing only name.

How text sorts

Text can be sorted too, letter by letter from A to Z:

1SELECT name2FROM products3ORDER BY name;

From A to Z: the backpack comes first and the water bottle last. Capital letters or not makes no difference to the order in MySQL.

Sorting by several columns

ORDER BY can take several columns, separated by commas: rows are sorted by the first column, and rows with the same first column are sorted by the second. Each column gets its own ASC or DESC:

1SELECT name, category, price2FROM products3ORDER BY category, price DESC;

Products of the same category end up together, and within a category the priciest come first. Press Step to see ORDER BY put the rows in a new order.

Tip: When the first column has repeated values, add one more column (id, say) to fix the order; otherwise which of those rows comes first is still up to the database.

The top few: LIMIT

LIMIT 3 keeps only the first 3 rows of the result, and it goes last. Together with ORDER BY, it gives you "the top 3". The 3 priciest products:

1SELECT name, price2FROM products3ORDER BY price DESC4LIMIT 3;

Press Step: first the rows are sorted from the highest price down, then LIMIT keeps only the first 3. LIMIT 3 without ORDER BY just means "any 3 rows", which is rarely what you want.

LIMIT is how MySQL writes it (SQLite and PostgreSQL accept it too). Other databases may write FETCH FIRST 3 ROWS ONLY (the standard way), and SQL Server writes SELECT TOP 3 ….

1SELECT name, stock2FROM products3ORDER BY stock4LIMIT 1;

Dropping repeats: DISTINCT

Which cities do the customers come from? Ask for city alone and the same city shows up several times:

1SELECT city2FROM customers;

SELECT DISTINCT keeps only one of each set of identical rows:

1SELECT DISTINCT city2FROM customers3ORDER BY city;

Five cities, each once, from Berlin to Tokyo. With several columns after DISTINCT, repeats are judged on all of them together: two rows are repeats only if every column is the same.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT name, stock2FROM products;

1SELECT id, order_date2FROM orders3ORDER BY order_date;

1SELECT customer_id2FROM orders;

What you learned

• Without ORDER BY, nothing promises the order of the rows; for a fixed order, write ORDER BY.
• ASC sorts small to large (and is the default), DESC large to small; text goes A to Z, but accented letters and other scripts may not land where you expect.
• ORDER BY a, b sorts by a, and rows with the same a by b; each column has its own direction.
• LIMIT n keeps the first n rows, and with ORDER BY gives "the top n"; other databases may write FETCH FIRST n ROWS ONLY or TOP n.
• SELECT DISTINCT drops identical rows.