Correlated Subqueries II

Hello Everyone,

I’m curious to know if this lesson would translate to Transact SQL or if this is only works in SQLite. I was running a variation of this query on a database and couldn’t get it to count. It would just throw a 1 in the aliased column for every row. I know we could just use ROW_NUMBER() OVER to accomplish the same thing, but I was just trying to follow the logic of the example in a T-SQL environment. Ideas?

  • Justin

SELECT origin, id,
FROM flights f
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;

So I answered my own question here. For some reason the ‘AND’ clause it the subquery was preventing the statement from counting. So yes, this logic does work in Transact SQL.