6. Correlated Subqueries II


#1

What is wrong with my code?

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

question is:
Using the same pattern, write a query to view flights by origin, flight id,
and sequence number. Alias the sequence number column as flight_sequence_number.

pattern was:
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

@ajaxace57441,
I think you want to create 4 columns

SELECT origin, id, carrier, <count selection>
using a comma , separator !!!!!

You are forgetting 1 comma ,


#3

indeed I had forget a comma but I also realized I don't need the carrier column. Yet, coding,

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

I had a result I believe it's not wrong however after runs it appears the following error message: FIND THE ORIGIN, FLIGHT ID, AND SEQUENCE NUMBER.


#4

Do I have to use GROUP BY OR ORDER BY OR both to make it right??


#5

@ajaxace57441,
You will have to use origin instead of you using carrier !!!!
You will not need an GROUP BY or ORDER BY

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;

For the fun of it, look at the result of

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;

#6

Thanks, I understood that in the second situation It is been ordered first by the name of origin in an alphabetical order and second by the sequence number; Point is, I ain't understand why is added 1 in the "f.origin=flights.origin) + 1" as in the example of the lesson?!


#7

9 posts were split to a new topic: 6. I must be missing something


#16

Please explain AND f.origin=flights.origin) + 1

many thanks!


#17

here is what i do no understand: in the where clause when would [f.id < flights.id] happen? aren't they the same field on the same table?? or is it that the flights.id is just one column because of the count(*) function?


#18

@rzagha1,
It will not be actually the answer, but to give you a feeling
https://discuss.codecademy.com/t/non-correlated-subqueries-iii-a-few-questions/40537/3?u=leonhard.wettengmx.n

  origin , id ,  flight_sequence_number
                         ^
                         ^
   +----------------+    |   +----------+
   |  flights as f  |  <==== | flights  |
   | virtual table  |        | table    |
   +----------------+        +----------+

#19