Chevrons Code

PHP, ASP.net, HTML, JavaScript, Yii and 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.