Prev: <resource-list> Deadlock graph interpretation
Next: query plan for stored procedure
From: BruceL on 3 May 2010 12:25
On May 3, 10:03 am, BruceL <bdlov...(a)gmail.com> wrote:
> Still don't see it as a set based solution then.

Based on Packing Joins in Celko's SQL for Smarties, I came up with
this solution:
create table Requests
(Requester varchar(20) not null primary key,seats int not null check
(seats >= 0));
Insert Requests Values ('Sam Sneed',1)
Insert Requests Values ('May West', 1)

select Ilist.requester,Olist.colony_name
from
(select C1.colony_name,sum(c2.max_registrations) -
c1.max_registrations ,sum(c2.max_registrations)
from Colonies C1, Colonies C2
where c2.colony_name <= c1.colony_name
group by c1.colony_name,c1.max_registrations)
as Olist (Colony_name,start_running_seats,end_running_seats)
inner join
(select r1.requester,sum(r2.seats)- r1.seats ,sum(r2.seats)
from requests as r1, requests as r2
where r2.requester <= r1.requester
group by r1.requester,r1.seats )
as Ilist (requester,start_running_seats,end_running_seats)
on olist.start_running_seats < ilist.end_running_seats
and olist.end_running_seats > ilist.start_running_seats


First  |  Prev  | 
Pages: 1 2
Prev: <resource-list> Deadlock graph interpretation
Next: query plan for stored procedure