How to select only row which has latest id in each group

[ketting00]

Hi guys,

I’ve a problem I can’t solve by myself again.

I’ve a table structured as below:

id		ref		name		type		value
1		1		....		A		xxxx
2		1		....		B		xxxx
3		2		....		A		xxxx
4		1		....		B		xxxx
5		3		....		A		xxxx

I want the output row to be representative of each type and look like this:

id		ref		name		type		value
5		3		....		A		xxxx
4		1		....		B		xxxx

Database: MySQL
Language: PHP

Can it be done in one query.

Hope the question is clear.

Thank you,

[r937]

SELECT t.id , t.ref , t.name , t.type , t.value FROM ( SELECT type , MAX(id) AS latest FROM daTable GROUP BY type ) AS m INNER JOIN daTable AS t ON t.type = m.type AND t.id = m.latest

[ketting00]

Thank you,

It appears to have worked.

I’m still learning the query pattern

[r937]

@ketting00

I’m still learning the query pattern

run the subquery by itself, inspect the results, and imagine the results are a separate table – now imagine this separate table joined back to your original table using those two join columns to identify which complete table rows to return