Course 9.2Lesson 3 of 3
ORDER BY sorts the output of a query by one field, in ascending (ASC) or descending (DESC) order. You sort numbers, text and dates, put ORDER BY together with WHERE, and work out the exact order of the output.
Try the code and the simulations beside the lesson.
A list of books is easier to use when it is sorted: the longest book first, or the titles from A to Z. ORDER BY sorts the output by a field.
ASC means ascending: smallest first, A to Z, earliest date first.DESC means descending: largest first, Z to A, latest date first.SELECT Title, Pages
FROM Books
ORDER BY Pages DESC;The output:
Title Pages
Deep Blue 410
Night Train 320
Iron Bridge 288
Lost Key 256
Green Hills 180
Snow Fox 144The table itself does not change. Only the output is sorted.
Text is sorted in alphabetical order, letter by letter. Deep Blue comes before Green Hills because D comes before G.
SELECT Title
FROM Books
ORDER BY Title ASC;The output:
Title
Deep Blue
Green Hills
Iron Bridge
Lost Key
Night Train
Snow FoxIf you leave out ASC and DESC, the output is sorted in ascending order. In an examination answer, write ASC or DESC every time, so your meaning is clear.
Dates sort from the earliest to the latest with ASC. In SQLite a date stored as text, year first, such as 2024-03-14, sorts in the right order.
A script can choose records and sort them. The clauses always come in this order:
SELECT the fieldsFROM the tableWHERE the conditionORDER BY the field and ASC or DESCSELECT Title, Pages
FROM Books
WHERE Genre = 'Nature'
ORDER BY Pages ASC;The output:
Title Pages
Snow Fox 144
Green Hills 180
Deep Blue 410Work it out in two steps. First keep the nature books: Green Hills, Deep Blue and Snow Fox. Then sort those three by pages, smallest first.
A script can also sort by a field that it does not output. SELECT Title FROM Books ORDER BY Pages DESC; lists only the titles, but the longest book is still first.
The program runs a script with WHERE and ORDER BY on the Books table and prints each row of the output with its place.
SELECT, FROM, WHERE, ORDER BY.WHERE, only the records that pass are sorted and output.ORDER BY field ASC sorts smallest first, A to Z. DESC sorts largest first, Z to A.SELECT, FROM, WHERE, ORDER BY.Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
SELECT Title FROM Books ORDER BY Pages ASC;?ORDER BY Title DESC?WHERE, ORDER BY, FROM, SELECT.SELECT BookID FROM Books WHERE Author = 'Silva' ORDER BY Pages DESC;?Title of every mystery book, from A to Z.Every lesson is open to read. Enrolling also lets you:
Log in to join the discussion.
Loading…
This lesson is free to read. Enroll free to keep your place and take the tests.