Select the top points of each category in SQLite

Question

I have the following table in SQLite:

category  | userId | points
----------|--------|---------
        25|     522|      380
        25|     487|      350
        25|     142|      100
        25|     385|      500
        26|     521|      300
        26|     524|      100
        26|     366|      880
        43|     123|      310
        43|     587|      340
        43|     935|       90
        43|     625|       85

I want to select the TOPs points of each category and have already tried in several ways without success.

For example:

Select distinct (category), userId, points
from RecordPoints order by category, points DESC

Expected outcome:

category  | userId | points
----------|--------|---------
        25|     385|      500
        26|     366|      880
        43|     587|      340

But the query result is not as expected as above.

Answer 1

If you want the top value in each category, then you can use where and a correlated subquery:

select rp.*
from RecordPoints rp
where rp.points = (select max(rp2.points)
                   from RecordPoints rp
                   where rp2.category = rp.category
                  );

Answer 2

In SQLite 3.7.11 or later, you can use MAX() to select an entire row from a group:

SELECT category, userId, max(points)
FROM RecordPoints
GROUP BY category;

Answer 3

Try this:

SELECT rp.*
FROM RecordPoints rp
WHERE rp.points = (SELECT MAX(rps.points)
  FROM RecordPoints rps
  WHERE rps.category = rp.category);

OR

SELECT rp.*
FROM RecordPoints rp
JOIN (SELECT rps.category, MAX(rps.points) points
FROM RecordPoints rps GROUP BY rps.category) AS rp1 
ON (rp.category =   rp1.category AND rp.points = rp1.points);

Answer 4

(Posted on behalf of the OP).

Thanks for all, I use max and group by, problem is solved :D