Correlated subqueries II


#1


https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-ii


I understand that f is a virtual table and flights is the actual table, but I don't understand WHERE f.id < flights.id. Does it mean that sql will compare each row in f with each row in flights that have the same carrier?

this is what I thought:

compare MQ 17107 with MQ 7869,
compare MQ 17107 with MQ 2205,
compare MQ 17107 with MQ 14979
……
compare MQ 7869 with MQ 2205,
compare MQ 7869 with MQ 14979
……

But what does the COUNT(*) really count? And why the +1?


SELECT carrier, id,
    (SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
 AS flight_sequence_number
FROM flights;

Thanks in advance.


#2

Yes, sql will compare each row in f with each row in flights that have the same carrier. When they have the same carrier, it will check to see if the id number of the row in f is less than the id number in flights. If this is true it will add one to the new column called flight_sequence_number.

The idea is that this will give you the number of flights that have come before that flight for each carrier.


#3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.