Correlated subqueries II

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>

<Below this line, add a link to the EXACT exercise that you are stuck at.>
https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-ii

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>
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?

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

``` 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;
<do not remove the three backticks above>

Thanks in advance.
1 Like

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.

5 Likes

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