SELECT, WHERE, ORDER BY and LIMIT

The shape of a query

Every query in this lesson follows the same pattern:

SELECT columns
FROM table
WHERE condition
ORDER BY column
LIMIT number;

Only SELECT and FROM are required. The examples use the students table from the first lesson.

Choosing columns with SELECT

SELECT name, city
FROM students;

This returns the name and city of all six students. SELECT * returns every column. It's handy for exploring, but in application code list the columns you need, so the query stays fast and doesn't break when columns are added.

You can rename a column in the result with AS:

SELECT name AS student, marks AS score
FROM students;

Choosing rows with WHERE

WHERE keeps only the rows for which a condition is true:

SELECT name, marks
FROM students
WHERE city = 'Kochi';

Comparison operators: =, <> (not equal; != also works in PostgreSQL), >, <, >=, <=.

SELECT name, year
FROM students
WHERE year = 2;

Sorting with ORDER BY

Without ORDER BY, the database may return rows in any order. Always sort when order matters:

SELECT name, marks
FROM students
ORDER BY marks DESC;

ASC (ascending) is the default; DESC sorts from highest to lowest. You can sort by several columns: ORDER BY city ASC, marks DESC sorts by city, then by marks within each city.

Limiting results with LIMIT

LIMIT returns only the first n rows, which is perfect for "top" lists:

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;

OFFSET skips rows, which lets you show results page by page:

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3 OFFSET 3;   -- the 4th to 6th highest: Kiran Rao (77), Ravi Kumar (72), Arjun Das (64)

The order SQL evaluates a query

You write SELECT first, but the database logically processes the clauses in this order:

That's why LIMIT combined with ORDER BY gives "the top three": the rows are sorted before they are cut.