CODEWITHSUNDEEP
mysqlsql
DrXCheng
DrXCheng

Reputation: 4132

Is there a way to select a certain number of row for a certain column?

I have this table:

+----+---------+----------+-------+
| id | user_id | other_id | score |
+----+---------+----------+-------+
| 1  | 1       | 1        | 80    |
| 2  | 1       | 2        | 40    |
| 3  | 1       | 3        | 60    |
| 4  | 2       | 2        | 70    |
| 5  | 2       | 4        | 50    |
| 6  | 2       | 3        | 40    |
| 7  | 2       | 5        | 90    |
+----+---------+----------+-------+

I want to sort each user_id by score and only return maximum n (e.g., 2) rows for each user_id. So I want to get the result as:

+---+---+---+----+
| 1 | 1 | 1 | 80 |
| 3 | 1 | 3 | 60 |
| 7 | 2 | 5 | 90 |
| 4 | 2 | 2 | 70 |
+---+---+---+----+

Upvotes: 1

Views: 71

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 1549

select id , user_id , other_id , score from (select id , user_id , other_id , score,if(@tempvar=user_id,@i:=@i+1,@i:=1) as occurrence, @tempvar:=user_id  from (select * from (select  id , user_id , other_id , score from tablename order by score desc)t order by user_id )tempA,(select @tempvar='',@i=0)tempB)tempc where occurrence<3 group by user_id,occurrence

I tried with your data and get expected result. Just replace tablename to your table name and try above query

Upvotes: 0

MinhD
MinhD

Reputation: 1810

Try this:

SELECT
  id, user_id, other_id, score
FROM
  yourtable
WHERE
  score = (SELECT MAX(score) FROM yourtable a 
           WHERE a.user_id = yourtable.user_id 
             AND a.other_id= yourtable.other_id);

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

This can be easily done by using a subselect,comparing the count with same table 

SELECT *
FROM   Table1 t
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    Table1  t1
            WHERE t1.user_id = t.user_id AND 
                  t1.score >= t.score
        ) <= 2
ORDER BY t.user_id ASC,t.score DESC

Fiddle Demo

Upvotes: 4

Related Questions