### 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(person 1 have 2 values for the same date. now we just want to get either one of them.)

1, 23, 2013-01-05 or 1, 29, 2013-01-05

The previous solution I found was:

select personid, value, date_of_service from table test joinThe result actually contains 3 rows:

(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;

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 0We get:

else @sno+1

end as serialnumber,

@pid:=personid as personid, value, date_of_service

from test

order by personid, date_of_service desc

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!

## 1 Comments:

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