0

Here is my schema

enter image description here

and here is the result of my query

enter image description here

how can i remove duplicates and just get the values highlighted, i am trying to order by message time

With some Help

WITH CTE AS
(
    SELECT c.ContactID, c.Name, m.Text, m.Messagetime,
           RN = ROW_NUMBER() OVER (PARTITION BY c.ContactID
                                   ORDER BY m.MessageTime DESC)
    FROM dbo.Contacts c
    INNER JOIN Messages m ON c.ContactID = m.ContactID
)
SELECT ContactId, Name, Text, Messagetime
FROM CTE
WHERE RN = 1 order by messagetime

i managed to get the desired result, but how can i convert it so it runs on mysql

Regards

Community
  • 1
  • 1
Dakait
  • 2,531
  • 1
  • 25
  • 43
  • You cannot. Mysql doesn't support window functions. CTE is not a real problem here since it could easily be replaced with a nested query. – zerkms Jul 24 '14 at 23:16
  • @zerkms i know that is what im asking i hope the desired result can be achieved without CTE in mysql, please help my sql concept have rusted away... – Dakait Jul 24 '14 at 23:17
  • CTE is not a problem here - you could replace it with a nested query. But the window function is a problem. So what if you explain the task at first? – zerkms Jul 24 '14 at 23:18
  • window function? the distinct im using is wrong, i have highlighted the result portion i want, basically i want to get the contacts based on latest messages received (timestamp)... – Dakait Jul 24 '14 at 23:21
  • i hope i have made myself clear? :| i can send you the script if needed or a fiddle... – Dakait Jul 24 '14 at 23:25
  • You are looking for a `greatest-n-per-group` type of query, the duplicate should give you plenty of suggestions on how to resolve this. – Taryn Jul 24 '14 at 23:29
  • @zerkms by window function you meant `ROW_NUMBER()` sry i didnt get you at first – Dakait Jul 24 '14 at 23:32

0 Answers0