limit and then group by in MySQL

Question

I have a mysql table having following structure :

payment_id(int auto_increment), 
emp_id(int), 
chargeTime(datetime), 
payment(double)

I need to have three latest (with respect to chargeTime) records of all emp_id. What should be the query?

Can anyone please help me? Without using cursor would be helpful.

Answer 1

Unfortunately MySQL does not have the handy analytical functions that other SQL languages have. You can simulate them, tough, using some left outer join tricks:

   SELECT t1.payment_id
         ,t1.emp_id
         ,t1.cargeTime
         ,t1.payment
   FROM emps t1
    LEFT OUTER JOIN emps t2
     ON t1.emp_id = t2.emp_id
    AND t1.chargeTime>=t2.chargetime
   GROUP BY t1.payment_id,t1.emp_id,t1.cargeTime,t1.payment
   HAVING count(1)<=3

This query should get you the last three (more recent) payments per emp_id. Should you need the first three, the AND t1.chargeTime>=t2.chargetime would be written as AND t1.chargeTime<=t2.chargetime.

You can test it here http://sqlfiddle.com/#!2/3c1f9/1/0 Note that I have defined chargeTime as int for fastness sake, but should work with datetimes too.

Answer 2

Try:

SELECT * 
FROM yourtable
ORDER BY chargeTime DESC 
LIMIT 3