If I understand your requirement correctly, you cannot use the TOP clause in this context. This is because the TOP clause returns the TOP 'n' rows of a
resultset which is the output of an entire query. Thus, the evaluation of the TOP clause is pretty much the last thing done when executing a query. You want the restriction done by grouping on 'Item_no' so you can't use the TOP clause on each group.
There is another way, though. Let's first look at a very useful technique for ranking a set of rows. Suppose you want to find the top 5 rows in your table by quantity without using the TOP clause (or SET ROWCOUNT if you are using SQL Server). Before we deal with the Item_no grouping issue, let's just do it for the whole table:
Code:
SELECT T1.Item_no, T1.Customer, T1.qty
FROM YourTable T1
INNER JOIN YourTable T2 ON T1.Qty <= T2.Qty
GROUP BY T1.Qty, T1.Item_no, T1.Customer
HAVING COUNT(*) <= 5 --change to whatever number you want
This works by partitioning your table into subsets of rows where for each row in your table we find the number of rows whose quantity is less than or equal to the quantity in the 'current' row. If that subset contains less than (or equal to) 5 rows, then the current row must be in the 'top' 5.
Now let's look at how to do this one Item_no group at a time. You do not specify what the primary key is for your table. I will assume a primary key called 'TransID' is present in your table.
This query works by using a correlated subquery modeled on the above query. The correlation is done on 'item_no', so the subquery is executed for each Item_No.
Code:
SELECT Item_no, Customer, qty
FROM YourTable T0
WHERE TransID IN (
SELECT T1.TransID
FROM YourTable T1
INNER JOIN YourTable T2 ON T1.Qty <= T2.Qty
WHERE T1.Item_no=T0.Item_no AND T2.Item_no=T0.Item_no
GROUP BY T1.Qty, T1.TransID
HAVING COUNT(*) <= 3) --change to 5
ORDER BY Item_no, Customer, qty DESC;
Note that this isn't very efficient. The subquery is executed more times than it really needs to be; the self join will become very expensive as the number of rows in the table increases.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
SELECT TOP !!!!
