Filtering and sorting

Ask for exactly what you need

Lesson one asked a table a question. This lesson makes the question sharper. You will calculate a new column, combine several tests, and put the answer in a known order.

Show a calculation, and name it

You can write expressions, not just column names, in the select list. This query subtracts one hundred and twenty from each systolic reading. The as clause relabels the output column, so the result has a clear name. The as clause is optional, but it makes results easier to read.

• SELECT patient,
• systolic - 120 AS above_120
• FROM visits
• WHERE clinic = 'North';

AND, OR and NOT combine tests

The qualification can combine tests with the usual Boolean operators: and, or, and not. This query asks for rows from the South clinic, or any row with a systolic pressure over one hundred and fifty. Or keeps a row if either test is true, so four rows come back.

• SELECT patient, clinic, systolic
• FROM visits
• WHERE clinic = 'South'
• OR systolic > 150;

Sort the results, on purpose

To get rows in a known order, ask for it. Order by clinic, then by seen on, puts the North rows first, each in date order, then the South rows. The tutorial's advice: if the sort order is not fully specified, rows that tie may come back in either order. So name every column that matters.

• SELECT patient, clinic, seen_on
• FROM visits
• ORDER BY clinic, seen_on;

Partly sorted or fully sorted?

A sort that names only some columns leaves ties undecided. If two rows share the same clinic, either may come first. A sort that names every column that matters always gives the same order. That is the order you want when you check a result or compare two runs.

Check yourself

Pause and think. What does the as clause do in a select list? The answer: it relabels the output column.

• It relabels the output column
• It filters the rows
• It sorts the results
• It joins two tables

Keep these in mind

The select list can hold calculations, not just columns. As gives an output column a clear name. And, or and not combine tests in where. And order by sorts, so name every column that matters.

• The select list can hold calculations, not just columns
• AS gives an output column a clear name
• AND, OR and NOT combine tests in WHERE
• ORDER BY sorts; name every column that matters