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

From: Steve Kass <skass_at_drew.edu>
Date: Thu, 11 Jul 2002 23:11:13 -0400
Message-ID: <3D2E48D1.6858FABA@drew.edu>

John,

I'm not entirely sure what you want. I guessed that "concurrent" meant consecutive, so you want slot_id values of N, N+1 and N+2. If that's what you want, David's query won't assure that - it will find trainer_id, room_id pairs with three different slot_id values.

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.

(*) 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.

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 Thu Jul 11 2002 - 22:11:13 CDT