0

please find the below

https://www.db-fiddle.com/f/oDs9VD1T7ScSimA4RHwmzN/1

Hi, if you open the fiddle, i added test data,

Basically, the end result i am trying to get is, only one row from each custome_id order by last_updated and communication_id =1 Example Bellow Query gives all record order by updated_at DESC, but i need to extend it so that it works "Group by Customer_id" id and only show one row for each customer.

SELECT diary,customer_id,updated_at FROM test where communication_type_id=1 ORDER BY updated_at DESC;

the required result will be like this mark in bellow link

[https://ibb.co/qNJdsbb][1]

hope it make sense.

mysql version mysql Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))

Mikayeel Ayman
  • 3
  • 3
  • Please provide the desired result - although this appears to be a duplicate of the most frequently asked question under this tag – Strawberry Sep 19 '20 at 07:35
  • You aren't selecting anything in your last query and you should not be using group by without any aggregate functions. – P.Salmon Sep 19 '20 at 07:40
  • Does this answer your question? [Select row with most recent date per user](https://stackoverflow.com/questions/17038193/select-row-with-most-recent-date-per-user) – P.Salmon Sep 19 '20 at 07:42
  • @P.Salmon , the Required result will be mark in this image https://ibb.co/qNJdsbb – Mikayeel Ayman Sep 19 '20 at 08:13

1 Answers1

0

check this

select diary, customer_id, updated_at from (
  SELECT diary,customer_id,updated_at, row_number() over (partition by customer_id order by updated_at desc) rn 
  FROM `test`
  where communication_type_id=1 
  ORDER BY updated_at DESC) a
where rn = 1;
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72