Re: Help needed: Retrieve only rows where numbers are concurrent

From: Kieran Elby <kieran.elby_at_orbisuk.com>
Date: Fri, 12 Jul 2002 14:42:29 +0100
Message-ID: <3D2EDCC5.55910AE0@orbisuk.com>

Steve Kass wrote (snipped):
>
> (*) Kieran - your solution doesn't work in SQL Server, and I'm surprised
> it works in Informix. In the subquery I'd expect slot_id and t.slot_id both
> to refer to the inner table t, not the outer table t. That makes the count
> always zero, since slot_id - t.slot_id is always 0, so never > 0.

Are you sure? There isn't an inner table t in my query - I didn't specify an alias for test in the sub query:

select
  trainer_id,
  room_id,
  slot_id as first_slot
from
  test t
where
  (select count(*) from test where trainer_id = t.trainer_id and room_id = t.room_id and slot_id - t.slot_id > 0 and slot_id - t.slot_id < n) = n - 1
;

I guess it might have been clearer though had I written it thus:

select

  t1.trainer_id,
  t1.room_id,
  t1.slot_id as first_slot

from
  test t1
where
  (select count(*) from test t2 where t2.trainer_id = t1.trainer_id and t2.room_id
= t1.room_id and t2.slot_id - t1.slot_id > 0 and t2.slot_id - t1.slot_id < n) = n
- 1
;

Do you happen to know why doesn't it work in SQL Server, by the way?

Regards,
Kieran Elby Received on Fri Jul 12 2002 - 08:42:29 CDT