How to select DISTINCT rows without having the ORDER BY field selected
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;
delivers this result
pID
-------------
9
9
3
9
3
9
9
9
10
9
3
10 etc...
I开发者_如何学Python 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
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
You can't receive records in any predefined order if you don't use ORDER BY
because then the DB engine decides in what order to return them.
Try this:
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 s.sID DESC;
I.e. GROUP BY instead of DISTINCT should preserve order.
After using distinct "ORDER BY s.sID DESC;" will not work so try using somwthing like following
SELECT distinct(s.pID) as PID
FROM students s JOIN mentors m ON s.pID = m.pID
WHERE m.tags LIKE '%a%'
ORDER BY PID;
This will return >> 3, 9, 10
Use this
SELECT DISTINCT s.pID as PID
FROM students s JOIN mentors m ON s.pID = m.pID
WHERE m.tags LIKE '%a%'
ORDER BY s.sID DESC,1;
After struggling some more I have this
SELECT s.pID, MAX(s.sID) AS newest_student
FROM students s JOIN mentors m ON s.pID = m.pID
WHERE m.tags LIKE '%a%'
GROUP BY s.pID
ORDER BY newest_student DESC;
which gives me the required 9,3,10 but I have one useless field returned with it. I am hoping some-one will come with a better solution.
精彩评论