Lesson 1.4

ORDER BY and LIMIT: sort and keep the top rows

Sort results, break ties, place NULLs, and return just the top rows or one page.

Imagine you're a data analyst at Airbnb. The listings table has one row per place to stay.

1Sort with ORDER BY

ORDER BY sorts the result, smallest first. Add DESC for largest first.

SELECT name, city, price_per_night
FROM listings
ORDER BY price_per_night DESC;

Practice

List every listing's name in alphabetical order.

Return: listing id and name

Write your query here

Tip

Without ORDER BY, rows come back in no guaranteed order. If the order matters, always sort.

List more than one column: SQL sorts by the first, then uses the next one when values tie.

SELECT city, name, price_per_night
FROM listings
ORDER BY city, price_per_night DESC;

Practice

List the Lisbon listings, cheapest first. When prices tie, show the smaller listing_id first.

Return: listing id, name and price per night

Write your query here

Postgres puts NULLs last when sorting smallest first, and first with DESC. Add NULLS LAST or NULLS FIRST to choose.

SELECT name, review_score
FROM listings
ORDER BY review_score DESC NULLS LAST;

Practice

List the Tokyo listings from best to worst review score, with unrated listings at the end. Break ties with listing_id.

Return: listing id and review score

Write your query here

LIMIT n keeps the first n rows after sorting. Without ORDER BY, "top 5" just means any 5 rows.

SELECT name, city, price_per_night
FROM listings
ORDER BY price_per_night DESC
LIMIT 5;

Practice

Back at Netflix: find the 5 longest titles by duration_min. Break ties by title, A to Z.

Return: title and duration in minutes

Write your query here

OFFSET n skips the first n rows. Together with LIMIT, it gives you one page of results.

-- page 2, with 10 rows per page
SELECT listing_id, name
FROM listings
ORDER BY listing_id
LIMIT 10 OFFSET 10;

Practice

Show page 2 of the Paris listings sorted from cheapest, 5 listings per page. Break ties with listing_id.

Return: listing id and price per night

Write your query here