loading..
Русский    English
15:44

Simple SELECT statement page 3

Sorting can be accomplished by the columns absent from SELECT column-list. Naturally, these columns should be presented in the output of FROM clause. For example, to deduce the model list of PCs in the order from greatest price to lowest one, you can write

Console
Execute
  1. SELECT model FROM PC
  2. ORDER BY price DESC;

Notice that the price itself does not be returned by the query. Elimination of duplicates produces ambiguous situation that prevents the behaviour. Thus, the query

Console
Execute
  1. SELECT DISTINCT model FROM PC
  2. ORDER BY price DESC;
gives us the error yet:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

The same reason prevents from unerror working of the following query that uses grouping

Console
Execute
  1. SELECT model FROM PC
  2. GROUP BY model
  3. ORDER BY price DESC;

Column "PC.price" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

But if you eject ambiguity (i.e. to do sorting by an aggregate-function value for a group), the query will work:

Console
Execute
  1. SELECT model FROM PC
  2. GROUP BY model
  3. ORDER BY MAX(price) DESC;

Notes:

All the query examples (including erroneous ones) will work in MySQL, which eliminates ambiguity by itself. Do you want to know how? Look in MySQL documentation. :-)

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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
today
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100