CODEWITHSUNDEEP
sqlmysqldistinct
Jannie Theunissen
Jannie Theunissen

Reputation: 30124

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.

Upvotes: 3

Views: 7589

Answers (6)

Anthony Faull
Anthony Faull

Reputation: 18007

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

Upvotes: 7

Jannie Theunissen
Jannie Theunissen

Reputation: 30124

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.

Upvotes: 0

Veer
Veer

Reputation: 371

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;

Upvotes: 0

Salil
Salil

Reputation: 47542

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

Upvotes: 0

dkamins
dkamins

Reputation: 21948

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.

Upvotes: 1

wRAR
wRAR

Reputation: 25569

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.

Upvotes: 4

Related Questions