SQLite for Beginners: ORDER BY, ASC, DESC & Multi-Column Sorting | Episode 7

SQLite for Beginners: ORDER BY, ASC, DESC & Multi-Column Sorting

When querying a database, the order of results is not guaranteed unless explicitly specified. SQLite's ORDER BY clause lets you sort query results by one or more columns, either in ascending (ASC) or descending (DESC) order. You can also combine filtering with sorting to get precisely the data you want in the order you need.

Code

-- Create a table for songs in a music library
CREATE TABLE songs (
  id INTEGER PRIMARY KEY,
  title TEXT,
  artist TEXT,
  year INTEGER,
  duration INTEGER  -- duration in seconds
);

-- Insert sample songs
INSERT INTO songs (title, artist, year, duration) VALUES
  ('Song A', 'Artist X', 2010, 210),
  ('Song B', 'Artist Y', 2012, 180),
  ('Song C', 'Artist X', 2010, 200),
  ('Song D', 'Artist Z', 2015, 240),
  ('Song E', 'Artist Y', 2012, 190),
  ('Song F', 'Artist X', 2018, 230),
  ('Song G', 'Artist Z', 2015, 220),
  ('Song H', 'Artist Y', 2010, 205);

-- 1. ORDER BY year ascending (default)
SELECT * FROM songs
ORDER BY year;

-- 2. ORDER BY year descending
SELECT * FROM songs
ORDER BY year DESC;

-- 3. ORDER BY duration descending (longest songs first)
SELECT * FROM songs
ORDER BY duration DESC;

-- 4. ORDER BY artist alphabetically (A to Z)
SELECT * FROM songs
ORDER BY artist ASC;

-- 5. Multi-column sorting: artist ascending, then year descending to break ties
SELECT * FROM songs
ORDER BY artist ASC, year DESC;

-- 6. Combine WHERE and ORDER BY: songs by Artist Y, sorted by year descending
SELECT * FROM songs
WHERE artist = 'Artist Y'
ORDER BY year DESC;

Key Points

• The ORDER BY clause sorts query results; ascending order is default unless DESC is specified.
• You can sort by multiple columns to break ties, with the first column as primary and subsequent columns as tie-breakers.
• Text columns sort alphabetically (A to Z) by default when using ORDER BY ASC.
• Combining WHERE with ORDER BY filters results before sorting them.
• Sorting numeric columns like year or duration lets you control whether smallest or largest values appear first.