|
SQL for the Rest of Us
( Part 2 of 5 )
Sorting Results and Predicates
Rodney POWELL
Microsoft MVP - Excel |
|
This is the continuation of a five-part introduction to
Structured Query Language (SQL). For your convenience, here is a
link back to Part 1.
An ORDER BY clause in SQL uses a comma-delimited listing of
field names to designate the order of the result set. The following
example would retrieve all employee records using the
LastName field as the first sort key then the
FirstName field as the second sort key.
SELECT * FROM Employees
ORDER BY LastName, FirstName;
The default order is ascending. You can also request the records to
be returned in the reverse order by using the DESC keyword
after the clause.
SELECT * FROM Employees
ORDER BY Salary DESC;
You can also specify the sort by field positions rather than by the
field names.
SELECT LastName, FirstName FROM Employees
ORDER BY 1, 2;
There are four predicates that can be used in a SELECT
statement.
| Predicate |
Description |
| ALL |
Returns all records, even duplicates |
| DISTINCT |
Returns only unique records based on the fields specified in the SELECT statement |
| DISTINCTROW |
Returns only unique records based on all fields, even those not listed in the SELECT statement |
| TOP |
Returns the first n records or the top p percentage of records of the selected recordset |
The default predicate for a SELECT statement is ALL,
returning all records that meet the conditions of the query.
The DISTINCT predicate can be used to limit the result set to
unique combinations of the specified fields. The following example
returns a list of the unique states to which orders have been shipped.
SELECT DISTINCT ShipState FROM Orders;
If there is more than one field then only records unique across
all designated fields are included in the result set. The
following example would return a recordset showing a unique list of
which products and states among the orders.
SELECT DISTINCT ShipState, ProdID FROM Orders;
In contrast, the DISTINCTROW predicate omits data based on
entire duplicate records. Let's say you have somehow have
duplicate customer records in a table. You can use DISTINCTROW to
get a unique list of customers, as in the following example:
SELECT DISTINCTROW CompanyName FROM Customers;
The TOP predicate is used in conjunction with an ORDER BY
clause in two ways:
- TOP n records
SELECT TOP 10 CustID, TtlOrderAmt
FROM Orders
ORDER BY TtlOrderAmt DESC;
TOP does not necessarily imply "higher" or
"more." Think of it as the "first n records"
of the result set. For this reason, it's important to sort the
result set accordingly.
- TOP p PERCENT of the entire recordset
SELECT TOP 10 PERCENT CustID, TtlOrderAmt
FROM Orders
ORDER BY TtlOrderAmt DESC;
TOP p PERCENT operates similar to the TOP n
predicate, except the number of records isn't fixed.
Something to add?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
