Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    7

    Unanswered: Select query that bring maximum two rows for the same record in the field

    Hi there!

    I have the next table:

    ID NAME Points
    1 Name1 4
    2 Name1 3
    3 Name1 7
    4 Name2 9
    5 Name2 9
    6 Name2 6
    7 Name3 2
    8 Name3 7
    9 Name3 9
    10 Name3 1

    How to construct the SELECT query to bring maximum two records for the same name?

    Like so:

    1 Name1 4
    2 Name1 3
    3 Name2 9
    4 Name2 9
    5 Name3 2
    6 Name3 7

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you decide which two rows should be returned? If there are less than two rows, do you want to return any rows?

    -PatP

  3. #3
    Join Date
    Jun 2008
    Posts
    7
    Let's say I will return first two results with greatest points, like ORDER BY points DESC.
    If are less than two rows, return the single row found.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ID
         , NAME 
         , Points
      from daNextTable as T
     where ( select count(*) 
               from daNextTable  
              where NAME = T.NAME
                and Points > T.Points ) < 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Posts
    7
    This is not quite good, because if number of points are equal for each name, the query will bring all the records.
    That table with points was just an example. The points can have random values between 0-10.

    All I want is to bring maximum 2 instances of the same name, order by greatest points.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, yes, it is quite good

    ties are important, and if you ignore ties, your data is misleading

    one thing you could do is use my query, and then, if you still think it's the right thing to do, you could ignore ties when printing the results
    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
  •