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

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!
>

KraS -

If n is the number of consecutive slots you want, then this should do the trick:

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
;

Note this will only return the first slot in the sequence of n for each (trainer,room), however.

Regards,
Kieran Elby

PS: I've not tried this on SQL Server, but it works fine on Informix.

PPS: There might also be a clever way of doing it using GROUP BY and HAVING - for example,

select
  trainer_id,
  room_id,
  min(slot_id) as first_slot,
  count(slot_id) as num_slots
from
  test2 t
group by
  trainer_id,
  room_id
having

   max(slot_id) - min(slot_id) = count(slot_id) - 1    and count(slot_id) >= n;

is nearly what you want (but e.g. would fail to identify slots 101,102,103,105 as having 3 consecutive slots). Received on Thu Jul 11 2002 - 12:27:28 CEST