Lesson 2 of 22 · SQLite

Finding Witnesses

Find the witnesses in the SQL murder mystery with ORDER BY, LIMIT and LIKE.

The crime scene report mentions two witnesses.

Sorting and picking the top row

ORDER BY column DESC sorts from highest to lowest, and LIMIT 1 keeps only the first row. Together they answer "which is the biggest?" questions.

The second witness

The second witness is named Annabel and lives on Franklin Ave. Try this query too:

SELECT * FROM person
WHERE name LIKE 'Annabel%'
AND address_street_name = 'Franklin Ave';

Why filter on the street too? Run it without that line and see who else turns up.

Schema Reminder

  • person table has: id, name, license_id, address_number, address_street_name, ssn

Your challenge

Find the first witness: the person living in the highest-numbered house on Northwestern Dr. Return just that one person.

Solve it in your browser

Free, no sign-up. The SQLite database runs in your browser; your answer is checked instantly, with hints if you need them.