Pls, help. TSQL way, Limit the select of a result set by type and quantity

/*
Thanks in advance for your time and knowledge you would share.
Definition:
I have actions, that are of certain type, and workload of these actions to
process
The request comes as a recordset in request table.
How do I produce a record set that has in it quantity of records less or
equal to request quantities by type
I know that I can open a cursor, use (set rowcount @.memvar) and loop to
accumulate final resultset.
I know that if I define an identity field (or select into temp table with
identity) and count these records by selfjoining
But is there a better non-temp table solution?
Thanks again for your ideas
*/
set nocount on
declare @.work table (orderID int, status int, actionID int, primary key
(orderid, status,actionID))
declare @.actions table (actionID int, actionType int, primary key
(actionID))
declare @.request table (actionType int, RecQty int)
declare @.temp table (rec int identity, actionType int , orderID int, status
int, actionID int)
insert @.request values (1,3) -- deliver at least 3 records of this type
insert @.request values (2,8) -- deliver at least 8 records of this type
insert @.request values (3,3) -- deliver at least 3 records of this type
insert @.actions values (1, 1)
insert @.actions values (2, 1)
insert @.actions values (3, 2)
insert @.actions values (4, 2)
insert @.actions values (5, 2)
insert @.actions values (6, 3)
insert @.work values (1, 1, 1)
insert @.work values (1, 1, 2)
insert @.work values (1, 1, 3)
insert @.work values (2, 1, 1)
insert @.work values (2, 1, 2)
insert @.work values (2, 2, 3)
insert @.work values (2, 2, 4)
insert @.work values (2, 1, 6)
insert @.work values (3, 1, 1)
insert @.work values (3, 3, 2)
insert @.work values (3, 1, 5)
insert @.work values (3, 1, 6)
insert @.work values (4, 1, 1)
insert @.work values (4, 1, 2)
insert @.work values (4, 1, 5)
insert @.work values (4, 1, 6)
insert @.work values (4, 2, 6)
insert @.work values (4, 3, 6)
-- Possible solution
-- Total records
select actionType, count(*) recordcount
from @.actions a
join @.work w on w.actionID= a.actionID
group by actionType
insert @.temp (actionType, orderID, status, actionID)
select a.actionType, orderID, status, w.actionID
from @.actions a
join @.work w on w.actionID= a.actionID
-- This is what I am after, but any way not to use temp table as this would
cause procedure recompiles and this is often called one.
select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID, count(*)
from @.temp s1
join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
join @.request r on r.actionType = s1.actionType
group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
having count(*) <= r.RecQtyTry that based on the example of the oubs database:
DECLARE @.RANK INT
SET @.rank = 5
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
HAVING count(*) < @.Rank
To be found on:
http://support.microsoft.com/defaul...b;en-us;Q186133
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
--
"Farmer" <someone@.somewhere.com> schrieb im Newsbeitrag
news:Of0pCjyUFHA.544@.TK2MSFTNGP15.phx.gbl...
> /*
> Thanks in advance for your time and knowledge you would share.
> Definition:
> I have actions, that are of certain type, and workload of these actions to
> process
> The request comes as a recordset in request table.
> How do I produce a record set that has in it quantity of records less or
> equal to request quantities by type
> I know that I can open a cursor, use (set rowcount @.memvar) and loop to
> accumulate final resultset.
> I know that if I define an identity field (or select into temp table with
> identity) and count these records by selfjoining
> But is there a better non-temp table solution?
> Thanks again for your ideas
> */
> set nocount on
> declare @.work table (orderID int, status int, actionID int, primary key
> (orderid, status,actionID))
> declare @.actions table (actionID int, actionType int, primary key
> (actionID))
> declare @.request table (actionType int, RecQty int)
> declare @.temp table (rec int identity, actionType int , orderID int,
> status int, actionID int)
> insert @.request values (1,3) -- deliver at least 3 records of this type
> insert @.request values (2,8) -- deliver at least 8 records of this type
> insert @.request values (3,3) -- deliver at least 3 records of this type
>
> insert @.actions values (1, 1)
> insert @.actions values (2, 1)
> insert @.actions values (3, 2)
> insert @.actions values (4, 2)
> insert @.actions values (5, 2)
> insert @.actions values (6, 3)
> insert @.work values (1, 1, 1)
> insert @.work values (1, 1, 2)
> insert @.work values (1, 1, 3)
> insert @.work values (2, 1, 1)
> insert @.work values (2, 1, 2)
> insert @.work values (2, 2, 3)
> insert @.work values (2, 2, 4)
> insert @.work values (2, 1, 6)
> insert @.work values (3, 1, 1)
> insert @.work values (3, 3, 2)
> insert @.work values (3, 1, 5)
> insert @.work values (3, 1, 6)
> insert @.work values (4, 1, 1)
> insert @.work values (4, 1, 2)
> insert @.work values (4, 1, 5)
> insert @.work values (4, 1, 6)
> insert @.work values (4, 2, 6)
> insert @.work values (4, 3, 6)
> -- Possible solution
> -- Total records
> select actionType, count(*) recordcount
> from @.actions a
> join @.work w on w.actionID= a.actionID
> group by actionType
> insert @.temp (actionType, orderID, status, actionID)
> select a.actionType, orderID, status, w.actionID
> from @.actions a
> join @.work w on w.actionID= a.actionID
> -- This is what I am after, but any way not to use temp table as this
> would cause procedure recompiles and this is often called one.
> select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID,
> count(*)
> from @.temp s1
> join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
> join @.request r on r.actionType = s1.actionType
> group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
> having count(*) <= r.RecQty
>