-1

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.

Ravi Vishwakarma
  • 33
  • 1
  • 8
  • How can the results you show possibly be correct? The columns you list don't match the columns you have in your `SELECT` list. Importantly, you don't show what values you have for `created_at`, so it's hard to guess how you determine which rows you want to be returned. You're also relying on a bug in MySQL: selecting non-aggregate values that are not in the statement's `GROUP BY` clause. This necessarily results in ambiguity in the results. – MikeB Jan 06 '22 at 16:59
  • @Mike8 Ok I have added created_at in the table example, can you please check, and also can you please suggest any better query which can give me the expected result – Ravi Vishwakarma Jan 06 '22 at 17:09
  • Thanks. But I still can't quite figure out what you want to query. For `product_id == 3`, you return `id == 3`, even though `id == 22` has a newer `created_at` date. Which rows do you want to appear in your result set? Once you're able to explain what you want, I don't think it would be too hard to implement the query using a window function. – MikeB Jan 06 '22 at 17:31

2 Answers2

0

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 ..

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
-1

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.created_at) 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
Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
  • This won't return the results Ravi says he wants -- he said he wanted "only latest record", but you're sorting by the `id`, rather than the `created_at` field. Ravi says he wants `id ==36` to be in the result set, but your code doesn't get that result. Of course, Ravi's example results don't match what they've written, so maybe ... – MikeB Jan 06 '22 at 17:34