I'm trying to get data that have the same medicine_id and unique insurance_id and last inserted row. Put Group by and Order by but in that got random data not last inserted. I tried this code but got not last inserted data
SELECT
`m1`.`*`
FROM
(
`pricings` `m1`
LEFT JOIN `pricings` `m2` ON
(
(
(
`m1`.`medicine_id` = `m2`.`medicine_id`
)
)
)
)
WHERE m1.medicine_id = 2
group BY m1.insurance_id DESC
ORDER BY m1.created_at;
Here are the total rows. This is a full table
id | medicine_id | insurance_id | created_at |
---|---|---|---|
4311 | 2 | 1 | 2021-04-12 16:05:07 |
4766 | 2 | 1 | 2022-01-15 11:56:06 |
4767 | 2 | 38 | 2021-05-12 08:17:11 |
7177 | 2 | 38 | 2022-03-30 10:14:11 |
4313 | 2 | 39 | 2021-04-12 16:05:46 |
4768 | 2 | 39 | 2021-05-12 08:17:30 |
1356 | 2 | 40 | 2020-11-02 11:25:43 |
3764 | 2 | 40 | 2021-03-08 15:42:16 |
4769 | 2 | 40 | 2021-05-12 08:17:44 |
And I want to like this
id | medicine_id | insurance_id | created_at |
---|---|---|---|
4766 | 2 | 1 | 2022-01-15 11:56:06 |
4768 | 2 | 39 | 2021-05-12 08:17:30 |
4769 | 2 | 40 | 2021-05-12 08:17:44 |
7177 | 2 | 38 | 2022-03-30 10:14:11 |
MySQL 5.x: Use a sub-query to find the max created_at value per group, then join that on the source table to identify the row it was from.
MySQL 8: Use
ROW_NUMBER()
to enumerate each group, then pick the first row from each group.