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

koandy1983 · #1 (**permalink**) 06-09-08, 14:40

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

Pat Phelan · #2 (**permalink**) 06-09-08, 14:48

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

koandy1983 · #3 (**permalink**) 06-09-08, 14:51

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.

r937 · #4 (**permalink**) 06-09-08, 16:58

Code:

select ID
     , NAME 
     , Points
  from daNextTable as T
 where ( select count(*) 
           from daNextTable  
          where NAME = T.NAME
            and Points > T.Points ) < 2

koandy1983 · #5 (**permalink**) 06-10-08, 01:55

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.

r937 · #6 (**permalink**) 06-10-08, 05:58

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

Internet Services

Web Development

Digital Marketing

Consumer Tech