6. Correlated Subqueries II

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;

1 Like

@ajaxace57441,
I think you want to create 4 columns

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

You are forgetting 1 comma ,

2 Likes

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.

1 Like

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

1 Like

@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;
4 Likes

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?!

2 Likes

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

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

many thanks!

1 Like

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?

@rzagha1,
It will not be actually the answer, but to give you a feeling
Non-Correlated Subqueries III - A few questions

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

1 Like