ordering a table in Mysql, according to another, but without seeing repetitive rows of the first table

Question

In MySql, I have two tables, A and B.

A has as columns A.id, B has as columns B.id and B.aid.

or each row of A I have many rows of B. And the value of B.aid=A.id

of course.

Now I need to get a list of the values in A, but I need to order them, according to B.

In particular if I have two rows in A: a1 and a2. Each will have a series of rows in B: b11, b12, b13, ... and b21, b22, b23, ... Now I need to order the A from the one connected with the highest b.id to the one with the second highest, and so on. (of course having one row appearing only once).

I tried this:

SELECT a.id  FROM a, b WHERE a.id=b.aib ORDER BY b.id DESC

I did indeed got all the values in the right order, but each value of A would appear n times, if n was the number of rows in B related to that row in A.

How do I avoid that, so that I get only one value.

I am considering taking the wholelist, and then eliminating all the non unique values, but I fear that once the website becomes big it might not be doable anymore.

(In case you wonder, this is to program a fac-simile of a discussion board, the table A is the thread, and the table B is the entry, and I want to have a page where all the threads are presented, but in order of the thread that had the last action later)

Many thanks, Pietro

P.S. MySql is not my thing, so please do spell out the solution :)

UPDATE: The actual code is more complex, as it also involves users, and similar. So I am looking at something like:

SELECT DISTINCT a.id, a.question, a.roundid, a.phase, users.username, users.id  
FROM a, users, b 
WHERE a.phase = 0 AND users.id = a.usercreatorid AND b.experimentid = a.id 
ORDER BY b.id DESC

I tried the DISTINCT, as suggested below, but it does not work. I do get all the thread (i.e. questions) uniquely, but thhey are not perfectly ordered. I do not know why, but it seem he is not chosing a random row from b, and this goes generally in the right direction, but it is not the row with the max(b.id). SO the distinct does not sort between rows in the correct way. I will now look at the other solutions proposed.

Answer 1

As I understand it, you want the a.id values, ordered by the most recent corresponding b.id value.

Where you have a 1->many relation and need that sort of info, you're typically looking at a GROUP BY to aggregrate the data, or a subquery for more complex criteria.

So, something like this should do it, using a group by:

SELECT 
   a.id, a.question, a.roundid, a.phase, 
   users.username, users.id, 
   MAX(b.id) AS latest 
FROM 
   a, users, b 
WHERE 
   a.phase = 0 AND users.id = a.usercreatorid AND b.experimentid = a.id 
GROUP BY a.id
ORDER BY latest DESC

Answer 2

You want to use the DISTINCT keyword.

SELECT DISTINCT a.id  FROM a, b WHERE a.id=b.aib ORDER BY b.id DESC

Answer 3

select * from parent a 
order by ( select max(id) from child b where b.parent_id = a.id);

NOTE WELL: this is not a join, so you'll get all rows in a, not just those that have a child in b.

You can see why if you do this:

select *, ( select max(id) from child b where b.parent_id = a.id)
from parent a 
order by ( select max(id) from child b where b.parent_id = a.id);

(null sorts before anything else in an ascending sort.)

This avoids grouping or distincting, and has the advantage that the SQL pretty clearly states your intent, not a workaround to get at your intent, which makes it more self-commenting than some alternatives.