Typical mistakes at the solution of SELECT statement exercises

Tips and solutions

It is possible to solve the problem without use of a subquery. However, for this purpose non-standard means are used. The method is based on TOP N clause (SQL Server) which allows to extract from the sorted set the first N rows. Similar clauses are available in  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 dialects in many relational DBMS. In SQL Standardization Committee even the offer on inclusion of a similar construction in the language standard has been fixed. So it is not excluded, that by the moment when you are reading this book the given construction already will be standardized.

Here is the solution:

Console

Execute

SELECT TOP 1 WITH TIES model, price
FROM Printer
ORDER BY price DESC;

SELECT TOP 1 WITH TIES model, price FROM Printer ORDER BY price DESC;

    Scheme

So, sorting on decrease of the price is carried out. The result set gets one (the first - TOP 1 is used) row. However there is an issue when some printers in the table will have an identical ceiling price. The issue is being solved by means of WITH TIES clause, which will include in result set not only N rows (one in our case), but also all below going rows, for which values in column of sorting (price column in our example) coincide with values of N-th row (here is 1st).

In PostgreSQL/MySQL  for restriction of quantity of the rows returned by a query, the clause (following after ORDER BY) is used

LIMIT N [OFFSET M]

If OFFSET clause is absent, N rows, since the first, will be deduced, otherwise - N rows since M+1.

The features similar WITH TIES are not present in these DBMSs. Therefore for solving the above problem in a way through sorting anyhow it is necessary to use a subquery:

SELECT model, price
    FROM Printer WHERE price =
      (SELECT price FROM Printer ORDER BY price DESC LIMIT 1);

Let's notice that unlike MySQL, OFFSET clause in PostgreSQL can be used at absence of LIMIT clause also. In this case all the rows of a query will be returned except for the first M rows. So, for example, to obtain all the rows except for the first row with a ceiling price, it is possible to write:

SELECT model, price
    FROM Printer
    ORDER BY price DESC OFFSET 1;

When  A database management system (DBMS) by Microsoft Corporation.SQL Server is used the same problem can be solved so:

Console

Execute

SELECT model, price
FROM Printer WHERE code NOT IN(
SELECT TOP 1 code
FROM Printer
ORDER BY price DESC);

SELECT model, price FROM Printer where code NOT IN( SELECT TOP 1 code FROM Printer ORDER BY price DESC);

    Scheme

And now try to solve this problem under MySQL. :-)

At the solution of last problem it is necessary to pay attention to inaccurateness of its formulation. Therefore the presented solution on the same data can yield different results at different runs. In particular, there are available two rows with a ceiling price and which one will be deduced from them depends on the order in which DBMS will take rows. And this order can vary depending on the plan chosen by optimizer. To make statement and result(!) unequivocal, it is necessary to specify the unequivocal order of sorting in a condition of a problem. This unambiguity will be always provided by inclusion of a primary key at the end of the list of columns of ORDER BY clause, for example:

ORDER BY price DESC, code

ORDER BY price DESC, model, code

To return to discussion of exercise #10