Presenting Results: ORDER BY, LIMIT/OFFSET & Column Aliases

Ordering the result

A query’s rows come back in no guaranteed order unless you add ORDER BY. It sorts the *final* result and is the last clause processed (after SELECT):

SELECT name, salary FROM employees ORDER BY salary ASC;   -- ASC is the default

ASC sorts ascending (the default; you may omit it), DESC descending. You can sort by several columns — sort priority follows the listed order, so ORDER BY dept_id, salary DESC groups by department then orders each department’s rows by salary high-to-low. You may also sort by position number: ORDER BY 2 DESC sorts by the second column of the SELECT list (handy for aggregates with no name).

Renaming output with AS

AS gives an output column a readable label — essential for aggregate columns that would otherwise show a generated header like ?column?:

SELECT name AS employee, salary AS annual_pay FROM employees;

The alias names the *result* column; it does not rename the underlying table column.

Top-N and paging with LIMIT / OFFSET

LIMIT n caps the result to n rows. Combined with ORDER BY, it expresses Top-N:

SELECT name AS employee, salary AS annual_pay
FROM employees
ORDER BY salary DESC
LIMIT 3;          -- the three highest-paid employees

OFFSET m skips the first m rows — ORDER BY salary DESC LIMIT 10 OFFSET 10 is “page 2” of a 10-per-page listing. The SQL-standard spelling is FETCH FIRST n ROWS ONLY, which Postgres also accepts.

Worked example: a clean Top-N

To answer “who are the three best-paid employees, with friendly headers?” you combine all three tools — sort to define the ranking, alias for presentation, and limit to take the top:

SELECT name AS employee, salary AS annual_pay
FROM employees
ORDER BY salary DESC
LIMIT 3;

The order of operations matters: ORDER BY decides *which* rows are “top,” and LIMIT then takes that many off the sorted front. Swap the sort to ASC and the same shape gives you the lowest-paid three instead.