Retrieving the last record from each group in MySQL query which includes multiple joins-CodePudding

I have multiple tables product,quote,customer and i need to get only latest record from the group. I have seen following answer but this didn't work as I have multiple joins in my query Retrieving the last record in each group - MySQL

SELECT
    i.id,
    i.rate,
    i.created_at,
    i.product_id,
    p.name
FROM
    `quote_item` i
JOIN `quote` q ON
    i.quote_id = q.id
JOIN `product` p ON
    i.product_id = p.id
WHERE
    q.customer_id = 1
ORDER BY
    i.id
DESC

The above query gives following result

Id   rate   created_at           product_id  product_name
--------------------------------------------------------
36    450   2022-01-06 13:59:00     17          abc
23    400   2022-01-06 06:11:52     17          abc
22    400   2022-01-06 06:08:28     3           abc
21    350   2022-01-06 05:57:42     4           abc
17    150   2022-01-04 18:33:45     1           abc
3     300   2022-01-02 01:53:50     3           abc

Now when group by is used

SELECT
    i.id,
    i.rate,
    i.created_at,
    i.product_id,
    p.name
FROM
    `quote_item` i
left JOIN `quote` q ON
    i.quote_id = q.id
left JOIN `product` p ON
    i.product_id = p.id
WHERE
    q.customer_id = 1
GROUP BY
    p.id
ORDER BY
    i.id
ASC

It gives following result

Id   rate   created_at           product_id  product_name
--------------------------------------------------------
23    400   2022-01-06 06:11:52     17          abc
21    350   2022-01-06 05:57:42     4           abc
17    150   2022-01-04 18:33:45     1           abc
3     300   2022-01-02 01:53:50     3           abc

BUT THE EXPECTED RESULT IS AS FOLLOWS

Id   rate   created_at           product_id  product_name
--------------------------------------------------------
36    450   2022-01-06 13:59:00     17          abc
21    350   2022-01-06 05:57:42     4           abc
17    150   2022-01-04 18:33:45     1           abc
3     300   2022-01-02 01:53:50     3           abc

Any help will be appreciated.

CodePudding user response：

the use of left join table's column o where clause work as inner join then if you really need a left join you should move the condition in the rlated ON clause otherwise you should use an inner join in this case seems you are using an inner join

for get the max date you should join the subquery for max created_at and not use a group by improperly

SELECT
    i.id,
    i.rate,
    t.max_date,
    i.product_id,
    p.name
FROM `quote_item` i
INNER JOIN `quote` q ON  i.quote_id = q.id
INNER JOIN ( 
    select i.product_id, max(i.created_at) max_date 
    from `quote_item` i 
    group by i.product_id
) t on t.product_id = i.product_id and t.max_date = i.created_at
left JOIN `product` p ON  i.product_id = p.id
WHERE q.customer_id = 1

the use of group by for not aggregated column is not allowd for most of db and (by default) for mysql version >5.6 ..

CodePudding user response：

Use ROW_NUMBER() with Partiton By product_id

SELECT
    id,
    rate,
    created_at,
    product_id,
    name
FROM
  (SELECT
    i.id,
    i.rate,
    i.created_at,
    i.product_id,
    p.name,
    ROW_NUMBER()OVER(PARTITION BY i.product_id ORDER BY i.id) as seq
  FROM
    `quote_item` i
  JOIN `quote` q ON
    i.quote_id = q.id
  JOIN `product` p ON
    i.product_id = p.id
  WHERE
    q.customer_id = 1) T
WHERE seq = 1