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 Here is the solution: 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
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:
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:
When
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:
To return to discussion of exercise #10 To solve a problem on SQL-EX.RU Suggested exercises: 90 |