Select the rows which have the max/min value in a table. Mysql.

I think I am the first one to solve this problem, perfectly. At least, independently.

These 2 StockExchange questions describes the issue pretty well:

• How do I get the ID of a rows which have MAX and MIN values in SQL?
• Fetch the row which has the Max value for a column.

Here is a sample table:

personid, value, date_of_service
1,  23,  2013-01-05
2,  24,  2013-01-01
2,  25,  2013-01-02
1,  26,  2013-01-03
1,  27,  2013-01-04
1,  28,  2013-01-04
1,  29,  2013-01-05

Now we want to get the latest value for each person, that would be

2,  25,  2013-01-02
1,  23,  2013-01-05  or 1,  29,  2013-01-05

(person 1 have 2 values for the same date. now we just want to get either one of them.)

The previous solution I found was:

select personid, value, date_of_service from table test join
       (select personid, max(date_of_service) as maxdate from test
group by personid) a
on test.personid=a.personid and test.date_of_service=a.maxdate;

The result actually contains 3 rows:

1,  23,  2013-01-05
2,  25,  2013-01-02
1,  29,  2013-01-05

It is not bad. Get the job done. The join is not very efficient because the table was visited twice, and the join created (n*m) records. This is actually one of the official answers from MySQL. The other 2 official answers are the same good (or bad) as this one.

The two StackExchange pages give the concepts of "max(my_date) over (partition by userid) max_my_date" and "MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)", but they are Oracle or MS SQL Server concepts, not available in MySQL. With some more help from Xaprb and Madhivanan, I am able to come up with a clean solution:

First,

select @sno:= case when @pid<>personid then 0
                    else @sno+1
    end as serialnumber,
@pid:=personid as personid, value, date_of_service
from test
order by personid, date_of_service desc

We get:

serialnumber, personid, value, date_of_service
0, 1,  23,  2013-01-05
1, 1,  29,  2013-01-05
2, 1,  27,  2013-01-04
3, 1,  28,  2013-01-04
4, 1,  26,  2013-01-03
0, 2,  25,  2013-01-02
1, 2,  24,  2013-01-01

You can see that I added "rank" for each row of same person, order by date desc. Another person will have ranks, starting from 0 again.
So the final solution is

select * from ( the previous sql statement) where serialnumber=0

and we get:

0, 1,  23,  2013-01-05
0, 2,  25,  2013-01-02

Yeah! It works! Compare to the previous solution, this one only visit the table once.

If you want to get the earlest value, you just need to order by personid, date_of_service (without desc); If you want to the the date that it gets the highest value, change the order to personid, value desc. As long as you can make the serialnumber (rank) as 0 for your interest variable, you get it.

If you want to get the two record of the last day for person 1, (1,  23,  2013-01-05) and (1,  29,  2013-01-05), then you need to modify the sql not to grow @sno if the date is the same. You can do the homework.

Seriously, I think I am the first one to crack this problem in MySql.

Happy Hacking!