Re: Help needed: Retrieve only rows where numbers are concurrent
Date: Thu, 11 Jul 2002 23:11:13 -0400
Message-ID: <3D2E48D1.6858FABA_at_drew.edu>
John,
Kieran's solution (*) might be exactly what you want. It will give you the first of each sequence of three consecutive slot_id values, so if there is a sequence of 7, it will return the first 5 of them, since each of the first 5 starts a sequence of three in a row. Kieran's solution will also identify two sequences of three for the same (trainer_id, room_id) even if there are missing values intervening.
Here's a solution that gives you all the rows for any trainer_id, room_id pair in the table that is represented exactly three times and no more, and where the slot_id values are consecutive.
Both Kieran's and my solution can fail if there are duplicate rows in the table.
select
T1.trainer_id, T1.room_id, T1.slot_id
from T T1 join (
select trainer_id, room_id
from T
group by trainer_id, room_id
having max(slot_id) - min(slot_id) + 1 = count(slot_id) and count(slot_id) = 3
) T2
on T1.trainer_id = T2.trainer_id
and T1.room_id = T2.room_id
order by T1.trainer_id, T1.room_id, T1.slot_id
Steve Kass
Drew University
KrasS wrote:
> Hi,
>
> I have the following (temporary) table:
>
> trainer_id room_id slot_id
> ----------- ------- -----------
> 1 1 147
> 1 1 148
> 1 1 149
> 5 1 147
> 5 1 148
> 5 1 149
> 7 1 147
> 7 1 148
> 7 1 149
> 11 1 148
> 1 2 147
> 1 2 148
> 1 2 149
>
> etc...
>
> How should I query this table to retrieve a list where:
> trainer_id and room_id have 3 concurrent slot_id available, like:
>
> trainer_id room_id slot_id
> ----------- ------- -----------
> 1 1 147
> 1 1 148
> 1 1 149
>
> Eventually, I will use a variable to define the number of required
> concurrent slots, but I don't think that will change the structure of
> the query anyway.
>
> Thanks for helping me out!
>
> Kind regards,
>
> John Bakta
Received on Fri Jul 12 2002 - 05:11:13 CEST