MySql to get the latest duplicate records

Question

Possible duplicate:
Getting the last record in each group

Hi everyone, I get the table data as follows

ID FedTaxID RegularPay Payperiodnumber 1 562545366 500 1 2 562545366 501 1 3 562545366 5000 2 

I would like to receive the following data

  ID FedTaxID RegularPay Payperiodnumber 2 562545366 501 1 3 562545366 5000 2 

I have tried something like the following, but I am not getting the required result

 select max(id) ID,regularpay,fedtaxid,payperiodnumber from tblemployeegrosswagesn1 where fedtaxid="562545366" group by payperiodnumber having count(*) >= 1; 

Can anyone help me

Answer 1

This will give you the desired result:

 SELECT t.ID, t.FedTaxID, t.RegularPay, t.Payperiodnumber FROM tblemployeegrosswagesn1 t INNER JOIN (SELECT MAX(ID) AS MaxId, FedTaxID, Payperiodnumber FROM tblemployeegrosswagesn1 GROUP BY FedTaxID, Payperiodnumber) AS InnerQuery ON t.ID = InnerQuery.MaxId AND t.Payperiodnumber = InnerQuery.Payperiodnumber AND t.FedTaxID = InnerQuery.FedTaxID WHERE t.FedTaxID = '562545366'; 

I am intrigued by the published link newtover; In this case, however, you need the maximum identifier for each payperiodnumber, so you will have to adapt this a bit more. It will look something like this:

 SELECT t.Id, t.FedTaxId, t.RegularPay, t.Payperiodnumber FROM tblemployeegrosswagesn1 t LEFT JOIN tblemployeegrosswagesn1 t1 ON (t.FedTaxId = t1.FedTaxId AND t.Payperiodnumber = t1.PayperiodNumber AND t.id < t1.id) WHERE t1.ID IS NULL AND t.FedTaxId = '562545366' 

It is much easier to read. Many thanks to @BillKarwin for the neat set-based solution. Thank you for the opportunity to learn a new (and better if the link is placed) way to do something.