Correlated Subqueries II Logic

[aalkulaib]

Hello,

I am not getting the logic behind the sequence requested in the following:

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

Can someone translates to me what does f.id < flights.id mean? and also f.carrier-flights.carrier

I do know what the result should be but I want to understand the logic if it was put in a sentance rather than a code, what would be?

(I think It could be something like: the id of the assigned f is less than the flights id, and the carrier of the assigned f = flights carrier)

Thanks

[sasaran]

Hello,

I made a guide for getting through this exercise here, which goes through a lot of the logic:

Solution / Lesson for Correlated Subqueries II, 6/7

This particular lesson of codeacademy's isn't very good. Everything is being told and nothing is being taught, so I decided I would try to make a guide to help out other users like myself learn what is going on with SQL in this section. This is more of an approach of how to play with SQL in order to learn it. I encourage the user to play around to see things for themselves - the editor allows this and we should take advantage of that. It's long, but hopefully clearer. TL;DR it doesn't actuall…

but in essence, after going through those exercises the f.id < flights.id is essentially telling it to order the resulting table from the renamed table (flights AS f) in ascending order, it isn't actually about the logic of x < y.

[albionsrefuge]