loading..
Ðóññêèé    English
23:59

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:

  1. SELECT DISTINCT model FROM PC_
  2. WHERE price = (SELECT price FROM PC_ WHERE price IS NOT NULL ORDER BY price LIMIT 1);

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.

  1. SELECT DISTINCT model FROM PC_
  2. WHERE price = (SELECT price FROM PC_ ORDER BY price nulls last LIMIT 1);

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.

  1. SELECT DISTINCT model FROM PC_
  2. WHERE price = (SELECT price FROM PC_ ORDER BY price LIMIT 1);

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.

  1. SELECT a IS NULL AS a, b IS NULL AS b FROM (SELECT NULL AS a, 1 AS b) x;
gives us

a    b
1    0

Taking into account that 0 precedes 1 when sorting in ascending order, we can adapt for MySQL the solution obtained for PostgreSQL:

  1. SELECT DISTINCT model FROM PC_
  2. WHERE price = (SELECT price FROM PC_ ORDER BY price IS NULL, price LIMIT 1);

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:

  1. SELECT code, model,price, ROWNUM rn FROM PC_ ORDER BY price;

CODE MODEL  PRICE RN
  10 1260     350 10
   9 1232     350  9
   8 1232     350  8
   7 1232     400  7
   3 1233     600  3
   1 1232     600  1
   5 1121     850  5
   2 1121     850  2
   4 1121     850  4
   6 1233     950  6
  12 1233     970 12
  11 1233     980 11
  13 2112     NULL  13

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:

  1. SELECT price FROM(
  2. SELECT model,price FROM PC_ ORDER BY price
  3. ) X
  4. WHERE ROWNUM = 1;

As in the case of MySQL and PostgreSQL, we'll use this query for obtaining models which have price determined with aid of it.

  1. SELECT DISTINCT model FROM PC_ WHERE price =
  2. (SELECT price FROM(
  3. SELECT model,price FROM PC_ ORDER BY price
  4. ) X
  5. WHERE ROWNUM = 1
  6. );

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:

Console
Execute
  1. SELECT DISTINCT model FROM (
  2. SELECT model, Rank() OVER (ORDER BY price) rn FROM PC_
  3. WHERE price IS NOT NULL
  4. ) X WHERE rn =1;

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.