6. f.id < flights, f.origin = flights.origin


#1


I'm having problems understanding WHERE f.id < flights.id and AND f.origin = flights.origin) + 1 part of the exercise. Can anyone shed some light?


https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-ii?action=lesson_resume



SELECT origin, id,
            (SELECT COUNT(*)
             FROM flights f
                 WHERE f.id < flights.id
                   AND f.origin=flights.origin) + 1 
                    AS flight_sequence_number
FROM flights
ORDER BY 1,2
LIMIT 36;


#2

From what i'm reading in the code (haven't really done the sql here as I'm not fond of sqlite):

You want to select the # of flights where their IDs (assuming IDENTITY(1,1) int PK) are less than your current main select "flights" id. Then you're adding 1 to show what order the flight is pulled in (sequence order).

A less confusing way to read this could be:

SELECT
     origin
    ,id
    ,(
        SELECT COUNT(*) FROM flights previousFlights
        WHERE previousFlights.id < currentFlight.id
            AND previousFlights.origin = currentFlight.origin
     ) + 1 AS flightSequence
FROM flights currentFlight
ORDER BY currentFlight.origin, currentFlight.id
LIMIT 36;

#3

Thanks @randonuser, I think I understand the code your explaining to me, but I don't understand the why. I can pass the lesson but would like to fully understand the logic.

Why does previousFlights.id need to be less than currentFlight.id, and why does previousFlights.origin have to equal the currentFlight.origin?


#4

the subselect is simply counting what # the current flight is

we're joining in the where on origin so we can get all the flights of the same origin.
we're using previous id < current id since the id table is more than likely an int column marked as identity (1,1) meaning when you insert that column automatically assigns the next available int to it.

So in total within the subselect, we're getting the # of previous flights, and adding 1 so we can show that the current flight is flight # 10 or 11 or 12 , etc , to the destination.

a more concrete example:

flights:
ID | origin | time
1 | AK | 0820
2 | AR | 0830
3 | AK | 0900
4 | AK | 0930
5 | AR | 0935

The select above would output the following:

AK - 1 - 1
AK - 3 - 2
AK - 4 - 3
AR - 2 - 1
AR - 5 - 2

this way we see the origins grouped together, their original ID, and the order of which they will be flying.


#5

Thanks, that completely answers what I was trying to understand. I was wondering why I saw a bunch of 1's and then a bunch of 2s etc. Now I see the full picture. The way the question was worded had me confused. I've taken your info and put used a code below to help visualize.

SELECT
     origin, id,
     (
         SELECT COUNT(*) FROM flights previousFlights
          WHERE previousFlights.id < currentFlight.id
            AND previousFlights.origin = currentFlight.origin
     ) + 1   AS flightSequence
FROM flights currentFlight
WHERE origin = 'ATL'
ORDER BY currentFlight.origin, currentFlight.id;

#6

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