0

sample data - table A

I only want all the list_id and item from max of purchase_date.

Based on the sample data above, I want to get the below records only.

record I want

I tried

select distinct
  customer_id,
  category_id,
  max(purchase_date) as max_date,
  list_id,
  item
from A
group by customer_id, category_id, list_id, item

and

select distinct
  A.customer_id,
  A.category_id,
  B.max_date,
  A.list_id,
  A.item 
from A
join (
  select distinct
    customer_id,
    category_id,
    max(purchase_date) as max_date
  from A
  group by customer_id, category_id
) B on A.customer_id = B.customer_id
    and A.category_id = B.category_id
    and A.purchase_date = B.max_date

they both don't work.

MT0
  • 143,790
  • 11
  • 59
  • 117
Amanda
  • 1
  • 1
  • _"they both don't work."_ Why not? Do you get a syntax error do they display extra records? – Marc Apr 21 '23 at 00:11
  • No I meant the first method still shows almost all rows. Second Method exceed run time. So for some reasons, they both cant get what i want. – Amanda Apr 21 '23 at 00:20
  • 1
    As per the [ask], please [**do not** post images of code, data, error message, etc](https://meta.stackoverflow.com/questions/285551). Instead, copy or type the text into your question, [formatted as code](https://meta.stackoverflow.com/questions/251361#251362). Reserve the use of images for diagrams or demonstrating rendering bugs; things that are impossible to describe accurately via text. – Bohemian Apr 21 '23 at 00:41

0 Answers0