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