SQL basics: SELECT statement options
Published: 26 Jun 2002 20:27 BST
SELECT options refine results
The SQL SELECT statement has the widest variety of query options, which are used to control the way data is returned. These options come in the form of clauses, keywords, and functions.
A clause is a statement that modifies results. Clauses are not required statements, but refine what and how data is displayed. The WHERE clause in a query is one example.
A keyword triggers functionality within the database. These are sometimes required with queries, such as INTO and VALUES in the statement "INSERT INTO table_name (column1) VALUES ('data1');". We'll look at the DISTINCT keyword, which triggers some useful optional functionality.
Functions are built-in statements that apply logic to a result set. We'll cover several options of this type.
I've summarised the most common of these clauses, keywords, and functions below. I'll explain each one and give examples of usage in the sections that follow.
ORDER BY -- A clause that returns the result set in a sorted order based on specified columns
DISTINCT -- A keyword that returns only unique rows within a result set
COUNT -- A function that returns a numeric value which equals the number of rows matching your query
AVG -- A function that returns the numeric value that equals the average of the numbers in a specified column
SUM -- A function that adds the numbers in a specified column
MIN -- A function that returns the lowest non-null value in a column
MAX -- A function that returns the largest value in a column
GROUP BY -- A clause that summarises the results of a query function by column
Use ORDER BY to sort results
The ORDER BY clause allows your database to sort your results so that you don't have to do it "manually" in your application code. The ORDER BY clause must come at the end of a query statement.
Basic usage is as follows:
SELECT * FROM Contacts ORDER BY first_name;
You are free to use ORDER BY with any select statement that might return multiple rows. You can also use it in conjunction with other clauses:
SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN 'a' AND 'k' ORDER BY last_name;
You can give multiple columns to sort by. Precedence is given from left to right, so the order in which you list your columns is important.
SELECT * FROM Contacts ORDER BY company, last_name, first_name;
Results are displayed in ascending order by default, either numerically or alphabetically. You can change this behavior by including the DESC keyword following any column name in the ORDER BY clause. In the example below, the highest net_amount will be listed first (in descending order). If two or more rows contain the same net_amount value, they will be displayed showing first the entry with the last_name value that comes earliest in the alphabet, because the last_name column is still sorted in ascending order.
SELECT * FROM Sales ORDER BY net_amount DESC, last_name, first_name;
After running out of defined column names to sort by, most databases will then sort by the first column in the table and work toward the right. Implementation in this area varies though, so if the sort order is important, you should explicitly define which columns to use.
Another handy thing to note is that with the ORDER BY clause (as with the WHERE clause), the columns you are using to sort the results do not have to be part of the returned result set. The following example is perfectly valid, as long as all referenced columns exist in the table:
SELECT company, first_name, net_amount FROM Sales ORDER BY start_date, last_name;
DISTINCT returns unique results
The DISTINCT keyword returns only unique rows within a result set. You may need to find out, for example, which companies are represented in your Sales table, but you don't want to see every entry. You can use DISTINCT to return one row for each unique company name:
SELECT DISTINCT company FROM Sales;
When you use DISTINCT, it applies to all requested columns. If you want a list of all the salespeople in your table and the companies they represent but not every sales entry, you can use the following statement. Note that this may return several entries from the same company, etc. DISTINCT applies to the entire requested result set. SELECT DISTINCT company, last_name, first_name FROM Sales;
You can still use DISTINCT when narrowing and sorting your results, as with any SELECT statement. To determine what is displayed, the database first establishes whether the refined request matches a row, then applies the DISTINCT function. The ORDER BY clause is always processed after the entire result set has been determined. In the following example, only rows in which the net_amount is greater than 100 will be returned. Since DISTINCT keeps the first encountered row that matches the query result set and discards the rest, the net_amount column referenced in the ORDER BY statement may seemingly yield randomly ordered results.
SELECT DISTINCT company, last_name, first_name FROM Sales WHERE net_amount > 100 ORDER BY company, net_amount;
Functions apply logic
Functions that return a single value are referred to as aggregate functions. When accessing the results of one of the following aggregate functions from your application, the "field name" containing the results will be the actual function you used. For example, when parsing your database results, the key in your results array may look like one of the following:
$keyname = "COUNT(*)"; $resultkey = "AVG(net_amount)";
Previous
Did you find this article useful?
Digg
Slashdot
Del.ici.ous
Stumble
Reddit
Full Talkback thread
1 comment
