Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: How Can I obtain the Top 3 of a dataset

    If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.

    ddave

  2. #2
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Once you have your SQL set up to return the sales dollars for all of the locations, just add TOP 3 before the select:
    Code:
     SELECT TOP 3 Sum(Dollars) FROM MyTable;
    Note that you can also SELECT TOP n PERCENT.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    General solution to your problem:

    The TOP N clause in TSQL is useful, but is limited by the fact that it only applies to the entire recordset, and that it will not accept a variable as a parameter.

    This method will return any requested number of records for each group of one or more column values in a dataset. It uses a type of join called a Theta join, where the values in two datasets are compared, but do not necessarily have to be equal:

    Code:
     
    declare @N int
    set     @N = 5  --The number of records to return for each grouping.
    select  YourTable.YourColumns
    from    YourTable
            inner join YourTable ThetaTable
            on YourTable.GroupColumns = ThetaTable.GroupColumns
            and YourTable.SortColumn <= ThetaTable.SortColumn
    group by YourTable.YourColumns
    having count(*) <= @N
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    The TOP function does not work as blindman indicates as it gives me only the top 3 of 120+ records, not the top 3 for EACH person. I will try the Theta join. Thanks to both for replying.

    ddave

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, your queries are starting to look good

    but the join should be outdented

    use foo and bar if more than one column is intended (YourColumns alone does not show comma syntax)

    and shorter aliases make it easier to read
    Code:
    select  T.foo, T.bar
    from    YourTable as T
      inner join YourTable as Th
            on T.GroupColumns = Th.GroupColumns
            and T.SortColumn <= Th.SortColumn
    group by T.foo, T.bar
    having count(*) <= n
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    blindman, your queries are starting to look good
    Looks can be deceiving...it doesn't work

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(SiteId int, EmpId int, Sales money)
    GO
    
    INSERT INTO myTable99(SiteId, EmpId, Sales)
    SELECT 1, 1, 10.00 UNION ALL
    SELECT 2, 1, 15.00 UNION ALL
    SELECT 3, 1, 20.00 UNION ALL
    SELECT 4, 1, 50.00 UNION ALL
    SELECT 5, 1, 10.00 UNION ALL
    SELECT 6, 1, 5.00 UNION ALL
    SELECT 1, 2, 100.00 UNION ALL
    SELECT 2, 2, 1500.00 UNION ALL
    SELECT 3, 2, 2000.00 UNION ALL
    SELECT 4, 2, 5000.00 UNION ALL
    SELECT 5, 2, 1000.00 UNION ALL
    SELECT 6, 2, 500.00 UNION ALL
    SELECT 1, 3, 1.00 UNION ALL
    SELECT 2, 3, 1.50 UNION ALL
    SELECT 3, 3, 2.00 UNION ALL
    SELECT 4, 3, 5.00 UNION ALL
    SELECT 5, 3, 1.00 UNION ALL
    SELECT 6, 3, .50
    GO
    
     DECLARE @N int
         SET @N = 3  --The number of records to return for each grouping.
    
      SELECT a.SiteId, a.EmpId
        FROM myTable99 a 
        JOIN myTable99 b
          ON a.SiteId  = b.SiteId
         AND a.EmpId   = b.EmpId
         AND a.Sales < = b.Sales
    GROUP BY a.SiteId, a.EmpId
      HAVING COUNT(*) <= @N
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    I know I've seen this work somehow though
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least to me, it is a lot more intuitively obvious to do this via a sub-query, something like:
    Code:
     DECLARE @N int
         SET @N = 3  --The number of records to return for each grouping.
    
    SELECT a.SiteId, a.EmpId, a.Sales
       FROM myTable99 a 
       WHERE (SELECT Count(*)
          FROM myTable99 b
          WHERE  b.EmpId   = a.EmpId
             AND a.Sales  <= b.Sales) <= @N
       ORDER BY a.EmpID, a.Sales DESC, a.SiteID
    The problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    I know I've seen this work somehow though
    yeah, it's tricky

    here's what you were searching for --
    Code:
      select a.EmpId
           , a.SiteId
           , a.sales
        from myTable99 a
      inner
        join myTable99 b
          on a.EmpId  = b.EmpId
         and a.Sales <= b.Sales
      group 
          by a.EmpId
           , a.SiteId
           , a.sales
      having count(*) <= 3
      order 
          by a.EmpId
           , a.sales desc
    which produces the following (correct) results:
    Code:
    1	4	50.0000
    1	3	20.0000
    1	2	15.0000
    
    2	4	5000.0000
    2	3	2000.0000
    2	2	1500.0000
    
    3	4	5.0000
    3	3	2.0000
    3	2	1.5000
    pat, i like the subquery method too, i guess i just got used to the join solution after so many times showing pre-4.1 mysql people how to do it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I indents 'em as I wants 'em, thank you!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey thanks guys...

    And yes Pat, I agree it may be more intuitive...but have a look at the plans...it looks like Rudy's Join is more effecient...even with the group by.

    I would not have guessed this. Anyone care if I blog this? With appropriate references of course.

    It's just one of things I know can be done...and I forget how to contruct it.

    Anyway, here's the plans
    Attached Thumbnails Attached Thumbnails Plans.bmp  
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blog away

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, You've all been Blogged
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    OK, You've all been Blogged
    ta very much

    and usually i just ignore your frequent typos, but in this case, i must insist that you correct the spelling of my surname

    thanks in advance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    mia culpa...done
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    mia culpa...done
    thanks

    and now, would you mind changing the link so that it points to the correct person

    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
  •