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

And I want to like this

CodePudding user response：

Adding it as an answer as well. I have not tested it, just fix the formating to work with whatever version of databse you are working with and let me know of the results.

SELECT m1.id  , m1.Insurance_id , m1.medicine_id , max(m1,created_at)
FROM (
    `pricings` `m1` LEFT JOIN `pricings` `m2` ON `m1`.`medicine_id` = `m2`.`medicine_id`
    )
WHERE m1.medicine_id = 2 and m1.insurance_id in (1,39,40,38)
GROUP BY m1.insurance_id DESC
ORDER BY m1.created_at;

Edit. I also removed the 6 extra parenthesis, I don't see how they could be of any use

CodePudding user response：

You place a having condition like having created_at = max(created_at)

, so that you will only get one record which is latestly uploaded.

CodePudding user response：

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;