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:
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.
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:
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.
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.
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,
There are no problems with NULLs when using aggregate functions, as NULLs are not included in treatment. Although subquery ought to be used also:
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?