loading..
Русский    English
18:59

The sorting and NULL values page 1

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:

  1. 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
  1. SELECT * FROM PC_ ORDER BY price;
Console
Execute
  1. SELECT * FROM PC_ ORDER BY price DESC;

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
  1. SELECT TOP 1 WITH ties model FROM PC_ ORDER BY price;

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
  1. SELECT TOP 1 WITH ties model FROM PC_ WHERE price IS NOT NULL ORDER BY price;

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
  1. SELECT DISTINCT TOP 1 WITH ties model FROM PC_ WHERE price IS NOT NULL ORDER BY price;
(ORDER BY items must appear in the select list if SELECT DISTINCT is specified.)

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
  1. SELECT model FROM (
  2. SELECT DISTINCT TOP 1 WITH ties model, price FROM PC_ WHERE price IS NOT NULL ORDER BY price
  3. ) X;

Notes:

There are no problems with NULLs when using aggregate functions, as NULLs are not included in treatment. Although subquery ought to be used also:


Console
Execute
  1. SELECT DISTINCT model FROM PC_
  2. WHERE price = (SELECT MIN(price) FROM PC_);

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?

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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.