SELECT statement

Getting summarizing values

If a column in the ORDER BY clause allows nulls, you should take into account in the case of  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server that NULL values go in the beginning of the result set when sorting is done in ascending order, and in the end of the result set when sorting is done in descending order.

As NULL values are absent from the teaching databases available here (in view of conformity between these databases and ones used on the sql-ex.ru), the copy of PC table named PC_ was created with additional row containing NULL in the price column:

INSERT INTO PC_ VALUES(13,2112,600,64,8,'24x',NULL);

It should be noted that table design allows NULLs in the price column.

You can assure yourself in the said above by executing the pair of following queries.

Console

Execute

SELECT * FROM PC_ ORDER BY price;

select * from PC_ order by price;

    Scheme

Console

Execute

SELECT * FROM PC_ ORDER BY price DESC;

select * from PC_ order by price DESC;

    Scheme

Why could it be important? The matter is that when seeking extremal values, the method based on sorting is frequently used. Let's consider the following task, for example.

Find out the models of PCs having the lowest price.

Sometimes this task is being solved by the following way:

Console

Execute

SELECT TOP 1 WITH ties model FROM PC_ ORDER BY price;

select top 1 with ties model from PC_ order by price;

    Scheme

WITH TIES keywords are used to deduce all the models with the lowest price, not only one. But we get model 2112 with unknown price as a result instead of models 1232 and 1260 which have lowest price indeed. To obtain correct result we need to exclude PCs with unknown price.

Console

Execute

SELECT TOP 1 WITH ties model FROM PC_ WHERE price IS NOT NULL ORDER BY price;

select top 1 with ties model from PC_ where price is not null order by price;

    Scheme

Following this way, we encounter with another issue related with duplicates. The data contain two PCs of the model 1232 with the same lowest price, so the result set gives us the model 1232 twice. DISTINCT keyword can not be applied if SELECT list does not include column(s) used in the ORDER BY clause. The error we get says to us about it.

Console

Execute

SELECT DISTINCT TOP 1 WITH ties model FROM PC_ WHERE price IS NOT NULL ORDER BY price;

select DISTINCT top 1 with ties model from PC_ where price is not null order by price;

    Scheme

To get solution as required, we can include price in the select list and use the query obtained in such a way as a subquery. So,

Console

Execute

SELECT model FROM (
SELECT DISTINCT TOP 1 WITH ties model, price FROM PC_ WHERE price IS NOT NULL ORDER BY price
) X;

select model from ( select DISTINCT top 1 with ties model, price from PC_ where price is not null order by price ) X;

    Scheme

Console

Execute

SELECT DISTINCT model FROM PC_
WHERE price = (SELECT MIN(price) FROM PC_);

select DISTINCT model from PC_ where price = (select min(price) from PC_);

    Scheme

Note also that standard solution will work under all DBMSs, as it does not use specific features of a dialect.

By the way, can we use sorting-based method in other DBMS?