loading..
Русский    English
00:47

Simple SELECT statement page 2

It is possible to sort out the result set by a number of columns pointed out in the SELECT statement. For this purpose, the clause ORDER BY <list of fields> is used which is always the latest clause in the SELECT statement. In so doing, the sort column in list of fields may be specified as a name or a non negative integer representing the position of the name in SELECT list. For example, to sort the result set by RAM in descending order we can write

Console
Execute
  1. SELECT DISTINCT speed, ram
  2. FROM PC
  3. ORDER BY ram DESC;
or

Console
Execute
  1. SELECT DISTINCT speed, ram
  2. FROM PC
  3. ORDER BY 2 DESC;

The following result is the same for both above queries.

speed ram
600 128
750 128
800 128
900 128
450 64
500 64
450 32
500 32

The result set can be sorted in ascending order (ASC is assumed by default) or in descending order (DESC keyword).

Notes:

It is not recommended to use in applications the queries with sorting by numbers of columns. This is connected with the fact that the structure of a table can change over time, for example, as a result of addition/removal of columns. As consequence, the following query

Console
Execute
  1. SELECT *
  2. FROM PC
  3. ORDER BY 3;
can give absolutely another sequence or generally cause an error, referring to an absent column.

Sorting by two columns

Console
Execute
  1. SELECT DISTINCT speed, ram
  2. FROM PC
  3. ORDER BY ram DESC, speed DESC;
gives the following result:

speed ram
900 128
800 128
750 128
600 128
500 64
450 64
500 32
450 32

Horizontal restriction is realized by the clause WHERE <predicate> after the FROM clause. Now the result set will only include the rows from the record source for each of those the predicate returns TRUE. In other words, the predicate for each row is checked . For example, the query "get information about processor's speed and RAM amount for computers priced below $500" can be written as follows:

Console
Execute
  1. SELECT DISTINCT speed, ram
  2. FROM PC
  3. WHERE price < 500
  4. ORDER BY 2 DESC;

speed Ram
450 64
450 32
500 32

The latter query uses a comparison predicate with operator "<" (less than). Beside this operator, the following operators may be used: "=" (equal), ">" (greater than), ">="(greater or equal), "<=" (less or equal) and "<>" (not equal). Expressions in comparison predicates may include any columns from the tables listed in the FROM clause. Character strings and date/time constants are enclosed in single quotation marks.

Here are some examples of simple comparison predicates:

Predicate Description
price < 1000 Price is less than 1000
type = ‘laptop’ Product type is Laptop
cd = ‘24x’ 24-speed CD-ROM
color <> ’y’ Not-color printer
ram – 128 > 0 RAM amount is over 128 Mb
Price <= speed*2 Price does not exceed twice processor's speed

Suggested exercises: 1, 2, 3, 4, 5, 6, 9, 14, 31, 33, 42.

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100