18:23

Exercise #10 (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, priceFROM PrinterORDER BY price DESC;`

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]`
where
N - number of the first rows returned by a query in the given sorting order;
M - number of the rows rejected before the beginning of an output.

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;`

model price
1434 290.00
1433 270.00
1408 270.00
1401 150.00

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

Console
Execute
`SELECT model, priceFROM Printer WHERE code NOT IN(SELECT TOP 1 codeFROM PrinterORDER BY price DESC);`
I.e. we choose all the rows, except for that which goes a first one at sorting by price on decrease.

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`

or
`ORDER BY price DESC, model, code`

To solve a problem on SQL-EX.RU

Suggested exercises 90