Top 5 For each ID

[OfirSh]

I have a problem
I have a long table with many costumers, each costumer have between 70-100 bills how can i get for each one his top 5, all the replies giving me only the top 5 of the first.(MsSQL!)
10X Ofir

[campkev]

try adding group by customer to the end of the query

[campkev]

wait, scratch that. this is what you want

declare @i int
declare @j int
set @i = -1
select @j = Max(customerid) from tablename
WHILE @i < @j
BEGIN
   select @i = Min(customerid) from tablename where customerid > @i
   select top 5 billamount, customerid from tablename where customerid = @i  order by billamount desc 
END

there may be a better way to do it but this should get the job done

[pedpina]

Yes! For sure this is a better way to do that
I put this in a generic way:

select Q.* from Table1 T, (select Table2_ID, Table1_ID, value from Table2 ) Q 

where 
	T.Table1_ID = Q.Table1_ID and 
	Q.Table2_ID in 
	(select top 5 top5.Table2_ID from Table2 top5 
	where top5.Table1_ID = Q.Table1_ID 
	order by top5.value desc)
order by Q.Table1_ID asc, Q.value desc

[jaysapidocs]

Yes! For sure this is a better way to do that
I put this in a generic way:

select Q.* from Table1 T, (select Table2_ID, Table1_ID, value from Table2 ) Q 

where 
	T.Table1_ID = Q.Table1_ID and 
	Q.Table2_ID in 
	(select top 5 top5.Table2_ID from Table2 top5 
	where top5.Table1_ID = Q.Table1_ID 
	order by top5.value desc)
order by Q.Table1_ID asc, Q.value desc

I don't quite understand it yet but I really appreciate you guys posting this.

I'm trying to select the top 5 for each non-unique key in a table ordered by their date and this looks like it will really do the trick. (I think.)

So far this the best advice I've seen for this so thanks!