Correlated subqueries II

My question is why does dropping the ‘f’ and ‘flights’ (such as f.id and flights.id) from the query result in the ‘flight_sequence_number’ being 1 for every row. But if you add those back each row has a distinct ‘flight_sequence_number’.

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

Hi Jon,
What lesson is this from? is there a link?

+1 generally means you’re trying to add 1 to a field.

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

@textsolver90830,

If you remove the f and flights from f.id < flights.id, the comparison will become WHERE id < id, which will always return False. Thus, the COUNT will be be 0 for each row. Since you add the + 1 outside the parentheses, the value of of each row in flight_sequence_number will always be the count (which is 0) + 1.

Hope that helps clear things up!