0

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

4
Contributors
4
Replies
5
Views
11 Years
Discussion Span
Last Post by jaysapidocs
0

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

0

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
0

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!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.