CODEWITHSUNDEEP
mysqldatabaseselectgreatest-n-per-group
Simon Jensen
Simon Jensen

Reputation: 73

MySQL find top results for each group

I have searched a lot, but I can't find an answer or something near.

I have a table like this:

id              int(11) NO  PRI     auto_increment
attribute_id    int(11) YES         
user_id         int(11) YES         
result          int(11) YES             
x10             int(11) YES

I need about 5 results from each attribute_id.

attribute_id can be any number, so at first, I need to check for the attribute_id, and next I have to check for the results for each attribute_id.

I can't find out how I do this without any programming language other than MySQL, but I know you can.

Example:

attribute_id    result
1               200
1               149
1               123
2               322
2               321
2               300
3               ...
3               ...

And so on.

Upvotes: 0

Views: 253

Answers (2)

ajay Sharavat
ajay Sharavat

Reputation: 46

     SELECT     attribute_id, result
     FROM   TableName a
      WHERE 
    (
        SELECT  COUNT(*) 
        FROM    TableName b
        WHERE   a.attribute_id = b.attribute_id 
                AND a.result <= b.result
    ) <= 5 order by result DESC;

Upvotes: 0

John Woo
John Woo

Reputation: 263693

SELECT  attribute_id, result
FROM    TableName a
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    TableName b
            WHERE   a.attribute_id = b.attribute_id 
                    AND a.result <= b.result
        ) <= 5

Upvotes: 1

Related Questions