17:31

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical mistakes at the solution of SELECT statement exercises

Database «Airport»

Exercise #77

Find the dates where number of trips from town Rostov were the maximum. Result set: number of trips, date.

It seemed to me, that the formulation is extremely clear. When this exercise still was at the second stage, it did not cause any questions. The difference in class I suppose :-). Now however the necessity to answer similar questions arises so often that I had to write this FAQ.

Here a typical example of wrong query:

Console

Execute

SELECT MAX(superden.qty), superden.date 
  FROM 
    (SELECT COUNT(den.trip_no) AS qty, den.date 
       FROM 
         (SELECT DISTINCT trip_no, date FROM Pass_in_trip) AS den, 
            Trip WHERE trip.trip_no=den.trip_no AND 
            trip.town_from='Rostov' 
       GROUP BY den.date) AS superden 
    GROUP BY superden.date;

The subquery

Console

Execute

SELECT DISTINCT trip_no, date FROM Pass_in_trip;

defines the flights which have been carried out. DISTINCT here it is quite pertinent, because the combination {trip_no, date} is the same for the passengers that flew in one plane. The subquery is joining with the Trip table to select only the Rostov flights: trip.town_from ='Rostov '.

The grouping by date allows us to count up distribution of number of the Rostov flights by days. While all is true, but last step is absolutely senseless. What for one more grouping by date is needed if all is already grouped, i.e. result set includes only one row for each date?

It seems, that the author of the above solution thus tried to find a maximum, but has received the same set. Let us take sample distribution of flights number by dates:


2007-08-19	2
2007-08-20	2
2007-08-21	3

In accordance with the task formulation we should receive only one row:


2007-08-21	3

as the maximal value of number of flights (3) is reached at 2007-08-21, but we shall receive the same 3 rows as a result of last grouping by date.

I hope now that it is clear what you should do to solve this problem, and I'll not answer more letters on this occasion :-).

To solve the problem on SQL-EX.RU

mistakes MAX grouping Airport exercise 77

Previos [Database «Airport»]

[Exercise 93] Next

Last added:

Tags

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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database «Airport»

Exercise #77