如何在不选择ORDER BY字段的情况下选择DISTINCT行 [英] How to select DISTINCT rows without having the ORDER BY field selected
本文介绍了如何在不选择ORDER BY字段的情况下选择DISTINCT行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
因此,我有两个表格学生(PK sID)和导师(PK pID).此查询
So I have two tables students (PK sID) and mentors (PK pID). This query
SELECT s.pID
FROM students s JOIN mentors m ON s.pID = m.pID
WHERE m.tags LIKE '%a%'
ORDER BY s.sID DESC;
提供此结果
pID
-------------
9
9
3
9
3
9
9
9
10
9
3
10 etc...
我正在尝试以此顺序获取不同导师ID的列表,因此我正在寻找要生成的SQL
I am trying to get a list of distinct mentor ID's with this ordering so I am looking for the SQL to produce
pID
-------------
9
3
10
如果仅在SELECT子句中插入DISTINCT,则会得到意外的结果10、9、3(顺序错误).任何帮助表示赞赏.
If I simply insert a DISTINCT in the SELECT clause I get an unexpected result of 10, 9, 3 (wrong order). Any help much appreciated.
推荐答案
SELECT s.pID
FROM students s JOIN mentors m ON s.pID = m.pID
WHERE m.tags LIKE '%a%'
GROUP BY s.pID
ORDER BY MAX(s.sID) DESC
这篇关于如何在不选择ORDER BY字段的情况下选择DISTINCT行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文