SELECT statement

Getting summarizing values

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:

SELECT DISTINCT model FROM PC_
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.

SELECT DISTINCT model FROM PC_
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.

SELECT DISTINCT model FROM PC_
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.

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

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

SELECT DISTINCT model FROM PC_
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:

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

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:

SELECT price FROM(
SELECT model,price FROM PC_ ORDER BY price
) X
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.

SELECT DISTINCT model FROM PC_ WHERE price =
(SELECT price FROM(
SELECT model,price FROM PC_ ORDER BY price
) X
WHERE ROWNUM = 1
);

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

SELECT DISTINCT model FROM (
SELECT model, Rank() OVER (ORDER BY price) rn FROM PC_
WHERE price IS NOT NULL
) X WHERE rn =1;

SELECT DISTINCT model FROM ( select model, Rank() OVER (ORDER BY price) rn from PC_ WHERE price IS NOT NULL ) X WHERE rn =1;

    Scheme