Correlated Subqueries II


#1

I am stuck at the following problem in Correlated subqueries.
Can anyone explain me the whole logic behind this query?
...
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,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;


#2

What operation that is happening don't you understand?


#3

Here. What's going on?How are we calculating the sequence number?


#4

This statement is basically saying if the id of f is less than the id of flights and the carrier are equal add 1,


#5

But why are we doing so? we were just supposed to view the sequence number. Why are using this increment query. I am sorry but i am really not able to understand/


#6

These two lines,

Are the conditions the query should check for in order to view the sequence_number


#8

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

I thought I'd chime in with what I think is going on with the code. That's the actual answer for the question. Basically, you want to return the columns Origin, ID and FSN (flight sequence number). The first two are self-explanatory. For the FSN, you are essentially counting the total number of flights that have the same origin and when there is more than one, you are adding 1 to the FSN for the flight ID that is lower. So, the origin ABQ has only one flight that originates from there, and that one flight has 1 for its FSN. If there was more than one, the the flight that has the lower flight ID would have an FSN of 2. To see this for yourself, just add

ORDER BY origin;

at the end of the code, and you will be able to see that the origins that have more than one flight will have an FSN that increases by 1 for each flight ID that is lower.

Hope that helps!


#9

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