Select the highest last row

Question

I want to select the highest last row of each member.

ID     UID      POINT        DATE           TIME

1       1         5       2012-11-29      11:29:03    
2       2        10       2012-11-29      11:38:12    
3       1        10       2012-12-02      05:15:01    
4       3         5       2012-12-02      09:51:34    
5       2         5       2012-12-02      12:14:14    
6       3         5       2012-12-04      12:18:30
7       1         5       2012-12-05      06:00:51

So I want to select the ID, UID and POINT where the point is the higest of each user. The result should be:

ID     UID      POINT        DATE           TIME

2       2        10       2012-11-29      11:38:12    
3       1        10       2012-12-02      05:15:01      
6       3         5       2012-12-04      12:18:30

I tried with this:

SELECT distinct uid, point, id FROM `test` 
GROUP By uid ORDER BY date DESC, time DESC

AND

SELECT id, uid, point FROM `test` 
GROUP BY uid ORDER BY date DESC, time DESC

But I got the some wrong result:

4(3), 2(2), 1(1)

Answer 1

Try:

SELECT id, uid, MAX(point) FROM `test` GROUP BY uid ORDER BY date DESC, time DESC

Answer 2

This query will select the highest points for each user:

select uid, max(`points`)
from members
group by uid

and this will select the maximum id where the user has the maximum points:

select uid, max(id)
from members
where (uid, `points`) in (select uid, max(`points`)
                          from members
                          group by uid)
group by uid

and this is the final query that you need:

select members.*
from members
where (uid, id) in (
  select uid, max(id)
  from members
  where (uid, `points`) in (select uid, max(`points`)
                            from members
                            group by uid)
  group by uid)

that shows:

ID  UID  POINT  DATE        TIME
2   2    10     2012-11-29  11:38:12    
3   1    10     2012-12-02  05:15:01      
6   3    5      2012-12-04  12:18:30

this will also give the same result, and looks simpler:

SELECT s.*
FROM
  (SELECT members.*
   FROM members
   ORDER BY uid, points desc, id desc) s
GROUP BY uid

I think that it will always work, but it's not documented!

A little explanation for the last query: MySql allows you to select nonaggregated fields in a group by query. Here we are grouping by uid but selecting all columns: the documentation says that the values of the nonaggregated columns will be undetermined (it can be any value inside the group) but in fact MySql just returns the first encountered value. And since we are applying a group by with nonaggregated columns on an ordered subquery, the first encountered value is what you need.