How to choose the last unique value?

Question

MySQL

There is a table with data


From it you need to pull out the last records with a unique value of the column "counter_id" I'm trying

to make such a query

SELECT * FROM (SELECT * FROM countersRecords ORDER BY id DESC) AS gagaga GROUP BY gagaga.counter_id

As a result, 2 and 3 identifiers should be selected, and 1 and 3 are selected.

Code

SELECT * FROM countersRecords GROUP BY counter_id ORDER BY id ORDER BY id DESC

also gives no results.

Answer 1

SELECT r.id
FROM countersRecords r
WHERE r.id = (
    SELECT MAX(id)
    FROM countersRecords latest
    WHERE latest.counter_id = r.counter_id
) t

But it will not work very fast, especially if the table is large.