When getting a grouping by the major ID, it gives an error in the grouping

Question

I have this table (example the data is major)

+-------+----------+-----------------------+---------------------+
|  id   | region_id| code_id               |     created_at      |
+-------+----------+-----------------------+---------------------+
| 51015 |     1510 |                     2 | 2023-03-22 11:05:36 |
| 51016 |     1670 |                     2 | 2023-03-22 11:08:52 |
| 51017 |     1670 |                     1 | 2023-03-22 11:08:58 |
| 51018 |     1510 |                     2 | 2023-03-22 11:32:58 |
+-------+----------+-----------------------+---------------------+

What I want is to get the row with the largest ID and a unique region_id the expected result would be this.

+-------+-----------+---------+---------------------+
|  id   | region_id | code_id |     created_at      |
+-------+-----------+---------+---------------------+
| 51017 |      1670 |       1 | 2023-03-22 11:08:58 |
| 51018 |      1510 |       2 | 2023-03-22 11:32:58 |
+-------+-----------+---------+---------------------+

Run the SQL

SELECT MAX(sa.id) as id, sa.region_id, sa.code_id, MAX(sa.created_at) as created_at
FROM Serians AS sa
WHERE sa.region_id IN (1670,1510)
GROUP BY sa.region_id

But the result is this, is wrong.

+-------+-----------+---------+---------------------+
|  id   | region_id | code_id |     created_at      |
+-------+-----------+---------+---------------------+
| 51017 |      1670 |       2 | 2023-03-22 11:08:58 |
| 51018 |      1510 |       2 | 2023-03-22 11:32:58 |
+-------+-----------+---------+---------------------+

How do I get the right result?

Answer 1

To get the row with the largest ID and a unique region_id

You can change the query as below : DBFIDDLE Demo here

SELECT id, region_id, code_id, created_at
FROM Serians
WHERE (region_id, id) IN (
  SELECT region_id, MAX(id)
  FROM Serians
  WHERE region_id IN (1670, 1510)
  GROUP BY region_id
);

This shall give you the expected output :

+-------+-----------+---------+---------------------+
|  id   | region_id | code_id |     created_at      |
+-------+-----------+---------+---------------------+
| 51017 |      1670 |       1 | 2023-03-22 11:08:58 |
| 51018 |      1510 |       2 | 2023-03-22 11:32:58 |
+-------+-----------+---------+---------------------+

You can also accomplish the same using inner-join.

SELECT sa.id, sa.region_id, sa.code_id, sa.created_at
FROM Serians AS sa
INNER JOIN (
  SELECT region_id, MAX(id) AS max_id
  FROM Serians
  WHERE region_id IN (1670,1510)
  GROUP BY region_id
) AS sb ON sa.region_id = sb.region_id AND sa.id = sb.max_id
order by sa.code_id;

The demo is included in above DBFIDDLE