General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>Hi Guys ,
>I have a table with suppliercode,productcode,dollarsales
>
>I want the top 10 product codes for each suppliercode. Is this possible
>Can someone please help me.
>
>TIA
Here is one way, which allows for sorting on some other field, and having an ID distinct from productcode:
SELECT suppliercode, ID, productcode, dollarsales, Othersortfield FROM yourtable INTO CURSOR Cur1 NOFILTER ORDER BY suppliercode, dollarsales DESC, Othersortfield
SELECT *, RECNO() AS Recnum FROM Cur1 INTO CURSOR Cur2 NOFILTER ORDER BY Recnum
SELECT suppliercode, MIN(Recnum) FROM Cur2 INTO CURSOR Cur3 NOFILTER GROUP BY suppliercode
SELECT Cur2.suppliercode, Cur2.productcode, Cur2.dollarsales FROM Cur2 INNER JOIN Cur3 ON Cur2.suppliercode = Cur3.suppliercode AND Cur2.Recnum >= Cur3.Min_recnum AND Cur2.Recnum < Cur3.Min_recnum + 10 INTO CURSOR ResultCursor ORDER BY Cur2.suppliercode, dollarsales DESC, Othersortfield
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only