6 II - Do not understand the example

Get Help SQL
#1

Hi! I cannot understand how the example works:

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;

What is the value of f.id and f.carrier? I am trying to get them by doing this:

SELECT f.id FROM
(SELECT COUNT(*)
FROM flights f);

but it does not work(please explain why)

Also I am confused whether ‘f’ means 499 or ‘f’ means the same as ‘flights’.
Please help:(

1 Like
#2

hey, I will try to explain as clear as I can:
f is another flights table which it is used to find all id < flights.id remember this subquery is executed for each row of flights (the outer one, not f). let’s say id of first row is 1 carrier is ‘abc’, then you can think of the subquery as

SELECT COUNT(*)
FROM flights
WHERE id < 1
AND carrier='abc'

just my 2 cents, hope it helps

4 Likes
#3