MySQL - 按外键表获取不同的主列表行顺序 [英] MySQL - Get Distinct Primary Col Table Row Order By Foreign Key Table

问题描述

标题可能有点混乱，这是我的架构

The title may be confusing, here is my schema

这是我查询的结果

如何删除重复项并突出显示值，我正在尝试按消息时间排序

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

问候

推荐答案

以下语法适用于 SQL Server 和 MySQL:

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.

这篇关于MySQL - 按外键表获取不同的主列表行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！