MySQL - Get Distinct Primary Col Table Row Order By Foreign Key Table

Question

The title may be confusing, 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

Regards

yes @TimSchmelter its sqlserver but i eventually have to run it against mysql — Dakait, Jul 24 '14 at 22:19
@PM77-1 i have to get the contact name based on the most recently received messages — Dakait, Jul 24 '14 at 22:20
`distinct` is ***not*** a function. It always operates on **all** columns of the select list. — , Jul 24 '14 at 22:24

Tim Schmelter · Answer 1 · 2014-07-24T22:25:14.510

1

One way in SQL-Server is using a ranking function like ROW_NUMBER:

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 ContadctId, Name, Text, Messagetime
FROM CTE
WHERE RN = 1

edited Jul 24 '14 at 22:25

answered Jul 24 '14 at 22:19

Tim Schmelter

450,073
74
686
939

A CTE is similar to a subquery, so you can use one. But if you ask because you want to use it in MySql, that won't work because MySql has no `Row_Number` function. You need to use `Group By`. But i have no time now to show you an example. – Tim Schmelter Jul 24 '14 at 22:21
tnx very much im gonna try it – Dakait Jul 24 '14 at 22:22
im getting error `The multi-part identifier "c.contactid" could not be bound.` – Dakait Jul 24 '14 at 22:23
@TimSchmelter tnx for your time :) – Dakait Jul 24 '14 at 22:30

Gordon Linoff · Accepted Answer · 2014-07-25T00:33:57.983

1

The following syntax will work in both SQL Server and MySQL:

SELECT c.ContactID, c.Name, m.Text, m.Messagetime
FROM Contacts c INNER JOIN
     Messages m
     ON c.ContactID = m.ContactID
WHERE NOT EXISTS (select 1
                  from messages m2
                  where m2.ContactId = m.ContactId and
                        m2.MessageTime > m.MessageTime
                 )
ORDER BY m.MessageTime desc;

Note that if you have duplicate most recent message times, then all will be returned.

edited Jul 25 '14 at 00:33

answered Jul 24 '14 at 23:53

Gordon Linoff

1,242,037
58
646
786

mysql has `Row_Number()`? – Dakait Jul 25 '14 at 00:16
just one thing how can i make it order by messagetime desc? please refer to the highlighted portion of the screenshot.... P.S a million thanks – Dakait Jul 25 '14 at 00:23

MySQL - Get Distinct Primary Col Table Row Order By Foreign Key Table

2 Answers2

Linked