Lesson 6/7 Table Transformation


#1

I cannot understand the intention behind the '+1' in the code and the whole idea behind this exercise. What is the purpose of the flight sequence number?

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

Correct Code:

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;


#2

+1 is used to give the sequence number to a flight within a group. In this example we are creating groups based on origin of a flight. Within a group we are ordering flights based on flight ids.

WHERE f.id < flights.id counts all the rows that are above the row currently being processed by the outer query, hence the current row will be the count+1, that's what the +1 is doing. Hope it helps.


#3

Thank you kartikjha for your response and clarification. However, I don't get the importance of this sequence number.


#4

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