Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: In Query : SELECT TOP 5 from INNER JOIN

    Hello,

    I have two tables one with items and another with invoices. I would like to take the items in my table and get the top 5 customers who have bought that item (in quantity shipped). I have tried this:

    SELECT qryPollyEnduranceItems.ITEM, qryInvoices.ICustName, SELECT TOP 5 (qryInvoices.SumOfIShipQty)
    FROM qryPollyEnduranceItems INNER JOIN qryInvoices ON qryPollyEnduranceItems.ITEM = qryInvoices.IItemNo
    ORDER BY qryPollyEnduranceItems.ITEM, qryInvoices.SumOfIShipQty DESC;

    But that doesn't work or

    SELECT qryPollyEnduranceItems.ITEM, qryInvoices.ICustName, qryInvoices.IShipQty
    FROM qryPollyEnduranceItems
    INNER JOIN qryInvoices ON qryPollyEnduranceItems.ITEM = qryInvoices.IItemNo
    WHERE qryInvoices.ICustName = (SELECT TOP 5 ICustName FROM qryInvoices)
    ORDER BY qryPollyEnduranceItems.ITEM, qryInvoices.IShipQty DESC;

    But I can't seem to hammer it out. Thanks in advance for any suggestions

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Describe your tables, provide sample data and required output.
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sounds like you're after method one here:

    http://support.microsoft.com/kb/210039/en-us
    Paul

  4. #4
    Join Date
    Mar 2008
    Posts
    4
    Table 1 Fields) Branch,Item, Desc1, Desc2, (qryPollyEnduranceItems)
    Table 2 Fields) InvDate, Item, Customer, Amt$, Qty, (qryInvoices [with totals])

    example, when I link the two tables I get:

    Item Customer Qty
    123 A 120
    123 B 100
    123 C 90
    123 D 80
    123 E 70
    123 F 60
    123 G 50
    124 A 200
    124 B 150
    124 C 130
    124 D 122
    124 E 110
    124 F 90
    124 G 75
    124 H 60

    But I would like it to return just the top 5 customers for each Item with the greatest quantities like:

    Item Customer Qty
    123 A 120
    123 B 100
    123 C 90
    123 D 80
    123 E 75
    124 A 200
    124 B 150
    124 C 130
    124 D 122
    124 E 110

    I would have to group the items and sort the quantities descending and then take the top 5 for each. Thanks in advance for any help

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT qryPollyEnduranceItems.ITEM
         , T.ICustName
         , T.IShipQty
      FROM qryPollyEnduranceItems 
    INNER 
      JOIN qryInvoices AS T
        ON T.IItemNo = qryPollyEnduranceItems.ITEM 
     WHERE ( SELECT COUNT(*)
               FROM qryInvoices
              WHERE IItemNo = T.IItemNo
                AND IShipQty > T.IShipQty ) < 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •