Common mistakes in solving SELECT statement exercises

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:

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

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:

LIMIT N [OFFSET M]

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:

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

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:

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

In  A database management system (DBMS) by Microsoft Corporation.SQL Server this task could be solved as follows:

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

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:

ORDER BY price DESC, code

ORDER BY price DESC, model, code