Friday, August 30

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:

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:

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!


At April 24, 2014 9:15 AM, Blogger MANOJ said...

May be u are.. :)

Any way good work mate..

Its serious time for MySQL team to consider about implementing RANK() and DENSE_RANK() as inbuilt functions.


<< Home