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 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.

Answers


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.


Need Your Help

MIPS Assembly: INT to STRING function only 2 chars (cut off last char)

assembly mips qtspim

I wrote this function to convert an int value to ascii string. But when the input is something like 315 it'll print 31 into the string.

Show/hide div input radio

javascript jquery html

I need to show/hide a div when a specific value is selected but I think the JavaScript selector 'or' doesn't not working. But its working when there is only one condition. I checked the documentati...