Typical mistakes at the solution of SELECT statement exercises

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, price
FROM Printer
ORDER BY price DESC;

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

    Scheme

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).

To return to discussion of exercise #10

To solve a problem on SQL-EX.RU