0

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.

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.

| improve this answer | |
1

Try:

SELECT * 
FROM yourtable
ORDER BY chargeTime DESC 
LIMIT 3
| improve this answer | |
  • This will get me three latest records with respect to chargetime. I would like to get three latest records wrt chargetime for all employees. – Praveen Prasannan Sep 22 '12 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.