Correlated SubQueries II
I’m having trouble comprehending what each line in the code is doing. It’s easiest to use the example instead of the actual question. Below is the code and I added the WHERE and ORDER BY line at the bottom of it to see what the result is doing easily. I understand the result, I don’t understand how the pieces are taking us there. I won’t be able to re-create this in a real life scenario if I ever needed to. Here is the prompt and code for it:
“It would also be interesting to order flights by giving them a sequence number based on time, by carrier.
For instance, assuming
flight_id increments with each additional flight, we could use the following query to view flights by carrier, flight id, and sequence number:”
SELECT carrier, id,
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
WHERE carrier = ‘AS’
ORDER BY 1,2 desc;
The results show that flight_id 1581 was the first time carrier “AS” flew and on flight_id 19963 was the 18th time carrier “AS” flew.
The +1 is so that it counts from 1 to 18 instead of 0 to 17.
The part I don’t understand is WHERE f.id < flights.id
I experimented a little:
< and > simply reverse which way it counts. For example with > flight_id 19963 becomes 1 and 1581 becomes 18.
= turns every flight_sequence_number into 2.
<= and => does the same as < and > but as if you had + 2, resulting in 2 to 19 for flight_sequence_numbers
Could someone please explain what is happening with WHERE f.id < flights.id? I can’t figure it out.