SQL query....

[stephlal]

I need to accomplish a select of records with a max rowcount. Let me try to explain.
I have over 2 million records that would of hit different strategy types and strategy ids. I want to simply select a population of 5 records(call them refno's) for every strategy type for every strategy ID.
I can't seem to get around this one.
example...
5 refno's for strategy id 80 for strategy type charles
5 refno's for strategy id 81 for strategy type charles
5 refno's for strategy id 97 for strategy type charles
5 refno's for strategy id 80 for strategy type john
5 refno's for strategy id 89 for strategy type john
etc...

SELECT refno, strategy type, strategy id
FROM strategy_table (all this data is in 1 table)
WHERE ?
GROUP BY strategy type, strategy id

I just can't wrap around this one.
set rowcount 5 command would do it, but I have a double condition. I need the rowcount 5 but for every strategy type and every strategy id.

I've been searching resources and on the web for 2 days and can't find anything on this....
Any help would be awesome.

Thanks

 

[gpadhy]

Stephlal,

I don't think what you are trying to do can be done with a single query, create this following stored procedure after changing real table name, column names, column types , variable name and variable types:

drop procedure test1
go

create procedure test1
as

create table #temp1 (strategy_type char(10), strategy_id int, rownum smallint, refno int)

declare @strategy_type char(10)
declare @strategy_id int
declare @rownum smallint
declare @smsecid int

declare outer_curs cursor for
select distinct strategy_type, strategy_id from strategy_table
open outer_curs

while (1=1)
begin

fetch outer_curs into @strategy_type, @strategy_id

if (@@sqlstatus <> 0)
begin
break
end

declare inner_curs cursor for
select refno from strategy_table
where strategy_type=@strategy_type and strategy_id=@strategy_id

open inner_curs
select @rownum=1
while (1=1)
begin

fetch inner_curs into @refno

if ((@@sqlstatus <> 0) or (@rownum > 5))
begin
close inner_curs
deallocate cursor inner_curs
break
end
insert into #temp1 values (strategy_type, strategy_id, rownum, refno)
select @rownum=@rownum+1
end
close inner_curs
deallocate cursor inner_curs

end
close outer_curs
deallocate cursor outer_curs

select * from #temp1

 

[stephlal]

Thanks for taking the time to write out the sp. I'm not an sp expert, but seems pretty straight forward. I'll give it a try.

Thanks