Re: Help needed: Retrieve only rows where numbers are concurrent
Date: Fri, 12 Jul 2002 14:42:29 +0100
Message-ID: <3D2EDCC5.55910AE0_at_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 - 15:42:29 CEST