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

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>
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?

<Below this line, add a link to the EXACT exercise that you are stuck at.>
https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-ii?action=lesson_resume

<Below this line, in what way does your code behave incorrectly? Include ALL error messages.>

<In this course, it often helps to include a screenshot of your whole web browser – that lets everyone see what you see. If you wish to include a screenshot, add it below this line.>

<If you wish to copy/paste in your code, you can use this next section. This will allow others to copy/paste your code for testing – something that they won’t be able to do with just a screenshot.>

``` 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;
<do not remove the three backticks above>

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;
6 Likes

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?

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.

6 Likes

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;
1 Like

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