Correlated Subqueries II


#1

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,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;


#2

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.


#3