Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 8th, 2003, 07:41 PM
Authorized User
 
Join Date: Jun 2003
Location: Shah Alam, Selangor, Malaysia.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT TOP !!!!

how do I select TOp 5 for every item on SQL Transact?

Example:
Item_no CUSTOMER qty
A1001 C001 10
A1001 C002 5
A1001 C003 25
A1001 C004 45
A1001 C005 15
A1002 C002 35
A1002 C006 15
A1002 C005 15
A1002 C004 25
A1002 C001 75


I NEED A RESULT AS :
A1001 C004 45
A1001 C003 25
A1001 C005 15
A1001 C001 10
A1001 C002 5
A1002 C001 75
A1002 C002 35
A1001 C004 25
A1002 C005 15
A1002 C006 15

REGARDS

JANE
  #2 (permalink)  
Old August 10th, 2003, 07:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old October 17th, 2003, 11:22 AM
Registered User
 
Join Date: Oct 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff, that is very clever and makes perfect sense once I tried it out and took apart the query. I'm glad I found your post. Very helpful, thank you!



Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT TOP n rgerald SQL Server 2000 3 May 12th, 2006 04:03 PM
select top problem keyvanjan SQL Server 2000 5 January 7th, 2006 07:11 AM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
SELECT TOP FROM HAVING khatfield29 SQL Language 1 August 23rd, 2004 02:41 PM
SELECT TOP ciko1973 SQL Language 5 August 8th, 2003 04:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.