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.
SELECT
p.`*`
FROM
`pricings` p
INNER JOIN
(
SELECT
`medicine_id`,
`insurance_id`,
MAX(created_at) AS `created_at`
FROM
`pricings`
GROUP BY
`medicine_id`,
`insurance_id`
)
p_max
ON p.`medicine_id` = p_max.`medicine_id`
AND p.`insurance_id` = p_max.`insurance_id`
AND p.`created_at` = p_max.`created_at`
WHERE
p.`medicine_id` = 2
ORDER BY
p.`created_at`;
MySQL 8: Use ROW_NUMBER() to enumerate each group, then pick the first row from each group.
SELECT
p.`*`
FROM
`pricings` p
FROM
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY `medicine_id`,
`insurance_id`
ORDER BY `created_at` DESC
)
AS `row_id`
FROM
`pricings`
)
p
WHERE
p.`medicine_id` = 2
AND p.`row_id` = 1
ORDER BY
p.`created_at`;