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 Servers 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, its quite possible this clause will be part of the standard by the time this book is published.

And thats the solution:

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

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 dont have a counterpart to WITH TIES. Therefore, to solve the analyzed exercise by sorting we have to use a subquery anyway:

  1. SELECT model, price
  2. FROM Printer WHERE price =
  3. (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:

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

model price
1434 290.00
1433 270.00
1408 270.00
1401 150.00

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

  1. SELECT model, price
  2. FROM Printer WHERE code NOT IN(
  3. SELECT TOP 1 code
  4. FROM Printer
  5. ORDER BY price DESC);

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 isnt 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:

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.