Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Yes. It worked. Thanks –  Praveen Prasannan Apr 25 '13 at 4:11

Try:

SELECT * 
FROM yourtable
ORDER BY chargeTime DESC 
LIMIT 3
share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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