SQLTeam.com | Weblogs | Forums

Exercise , please need help to understand this how can i work only with trip table only?

Find out the number of routes with the greatest number of flights (trips).
Notes.

  1. A - B and B - A are to be considered DIFFERENT routes.
  2. Use the Trip table only.

Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in).
ADVICE:- scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;

The result of correct query:

qty
4

Can you provide ddl and sample data and we can help with a solution?

1 Like

Mike hello again its like the other execerise u asked for ddl , i dont have it just have tu built a query to get the result 4 , i tried a lot of queris but got different numbers i dont know what is the problem.
Thanks for ur time :smiley:

Based on the extremely limited info available, this is what I think they meant:

SELECT TOP (1) COUNT(DISTINCT trip_no) AS Trip_Count
FROM Trip
GROUP BY town_from, town_to
ORDER BY COUNT(DISTINCT trip_no) DESC

Or you could do it without TOP (1) by adding an outer query and getting the MAX() of the Trip_Count.

1 Like

Thanks u sir , i appreciate and sorry for wasting ur time.
U make it look so easy and ur solution its very easy to understand. Thank u.
If i have more questions i will ask u :slight_smile: