The sorting and NULL values page 2
MySQL allows us to use DISTINCT without mandatory listing the sorting columns in SELECT clause. However MySQL has not analog of WITH TIES to solve the task extremely simply. Because of this we need to use subquery to deduce all the models with lowest price in the sorting-based method:
The same solution will work under PostgreSQL also, but the latter has a feature you usefully to know about. Namely, we can point where NULLs ought to be deduced - in the end or in the beginning of the result set. In view of solving our task, it is desired that the NULLs will go in the end of the sorted list. In this case we reduce the extra operation which eliminates NULL values.
By the way, NULL values go in the end of result set when sorting is performed in ascending order. Because of this NULLS LAST keywords which we used in the above query can be omitted in the solution of the task.
If we want that NULLs went in the beginning of the result set when sorting, we should write NULLS FIRST instead.
We could imitate in MySQL the behaviour of NULLS FIRST/LAST features. To do this, we'll use the fact that boolean data type in this DBMS is presented as TINYINT(1). Specifically, this means that 0 is corresponding to FALSE, whereas nonzero values are considered true. With so doing, a logical expression that is true represents 1, i.e.
Taking into account that 0 precedes 1 when sorting in ascending order, we can adapt for MySQL the solution obtained for PostgreSQL:
Similarly PostgreSQL, Oracle places NULLs in the end of the result set when sorting in the ascending order. There are NULLS FIRST/LAST also, but analog of LIMIT/TOP N for limiting the number of returned rows is absent.
To imitate the above approach to solution of the task in Oracle, we can use embedded function ROWNUM. The function performs numbering of the rows of a query, but does it after execution of FROM and WHERE clauses, i.e. before execution of SELECT and ORDER BY clauses. This can be illustrated by the result of the following query:
We can see that the row number does not correspond to sorting order. It is easy to verify that the numbering corresponds to the code column. This is due to the fact that optimizer uses index on this column when executing the query. So, to find out the lowest price on the base of sorting, we need to use subquery:
As in the case of MySQL and PostgreSQL, we'll use this query for obtaining models which have price determined with aid of it.
Solomon said: "In much wisdom is much grief: and he that increaseth knowledge increaseth sorrow."
Use standard solutions, I say. :-)
In conclusion, I want to tell about a way to solution which uses ranking functions. Solution idea is in ranking (RANK function is used) of the rows over ascending order of price with following choosing the (unique) rows having the rank of 1. The following query must work under any DBMS which support window functions: