17:20

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

SQL data manipulation language

SELECT statement

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

SELECT model FROM PC 
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

SELECT DISTINCT model FROM PC 
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

SELECT model FROM PC
GROUP BY model
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

SELECT model FROM PC
GROUP BY model
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. :-)

Pages

GROUP BY DISTINCT sorting ORDER BY

Previous [SELECT statement]

[Sorting in order of days of birth] Next

Last added:

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
month ago

SQL Problems and solutions

S. I. Moiseenko

SQL data manipulation language

SELECT statement

Simple SELECT statement page 3