September 16 2013

Retrieving the last record in each group

Tagged Under : ,

MySQL
Sometimes we keep all the records/logs in one table. But if we want retrieve each group last records in one SQL it look like impossible.

Below Example showed how to:
SELECT rs1.*
FROM TABLE rs1 LEFT JOIN TABLE rs2
  ON (rs1.name = rs2.name AND rs1.id < rs2.id)
WHERE rs2.id IS NULL;
Above method can get the last record results group by name.

If you want retrieve first records in each group, just change to
ON (rs1.name = rs2.name AND rs1.id > rs2.id)
The query become like below:
SELECT rs1.*
FROM TABLE rs1 LEFT JOIN TABLE rs2
  ON (rs1.name = rs2.name AND rs1.id > rs2.id)
WHERE rs2.id IS NULL;

But if the table had 100 thousand records. I suggest you use another method to retrieve it. because it will take more times to retrieved.

Make a Comment

*

code