Exercise #10 (tips and solutions)
It is quite possible to solve this task without using a subquery. However, you need non-standard features for that. The method is based on MS 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 Server’s TOP N clause that allows getting the first N rows from a sorted set. Similar clauses exist in SQL dialects of many relational DBMS. The SQL Standardization Committee even registered a proposal to include such a construct in the language standard. Thus, it’s quite possible this clause will be part of the standard by the time this book is published.
And that’s the solution:
As we see, the data is sorted by price in descending order. sorting on decrease of the price is carried out. One row (the first one, as TOP 1 is used) is included in the result set. However, there is the problem of having several printers with the same maximum price in the table. This is solved by using the WITH TIES clause that puts into the result set not just N rows (one in our case) but also records with values in the column(s) used for sorting (price for this example) matching the corresponding value(s) of the Nth row (the first one in this example).
PostgreSQL/MySQL use the following construct placed after ORDER BY for limiting the number of rows returned by a query:
Here, N is the number of first rows according to the specified sorting order to be returned by the query;
M is the number of rows to be skipped before output.
If OFFSET is omitted, the query returns N rows beginning with the first one; otherwise, N rows beginning with the row numbered M+1 are returned.
These DBMSs don’t have a counterpart to WITH TIES. Therefore, to solve the analyzed exercise by sorting we have to use a subquery anyway:
Let's note that unlike MySQL, PostgreSQL allows using OFFSET without LIMIT. In this case, all rows of the query will be returned except for the first M ones. Say, a query for displaying all rows except for the one with the maximum price could look like this:
In A database management system (DBMS) by Microsoft Corporation.SQL Server this task could be solved as follows:
I.e. we get all rows except for the one that goes first when sorted by price in descending order.
Now, try to master this task using MySQL. :-)
When solving this last task, note it isn’t formulated accurately enough. Therefore, the results returned by the solution above can vary from execution to execution even if the data remains the same. In particular, there are two rows containing the maximum price, and which one of them is displayed depends on the order the DBMS retrieves rows. And this order can change depending on the plan chosen by the query optimizer. To make the task and the result(!) unambiguous, it is necessary to specify an unequivocal sorting order for the task. This unambiguousness will always be ensured by including the primary key at the end of the list of columns to sort the rows by, for example:
Return to discussion of exercise #10
Suggested exercises: 90