D7 - db_select: order by most recent, commented status I'm writing my custom module.
I use the modules Statuses and 'fbss_comments' (part of the Statuses module). Comments are used to comment on statuses. 

Using the following code I get a list of results: 

 $query = db_select("statuses", "thread")-&gt;distinct();
        $query-&gt;join("fbss_comments", "comment", "comment.sid = thread.sid");
        $query-&gt;fields("thread", array("sid", "sender", "created", "message"));
        $query-&gt;fields("comment", array("created"));
        $query-&gt;orderBy("comment.created", "DESC");
        $result = $query-&gt;execute();


Now the list of results will be sorted by the most recent posted status. I want to achieve the following: sort the result list by statuses with the most recent comments

Some clarification: normally status(100) will be shown above status(30). When I comment on status(30), status(30) should be shown above status(100). On top of this, when I create a new status(101), this status should be shown on top of the result list.

I hope my goal is clear to you, sadly, I have no idea how to achieve this.

Any advice or help is much appreciated

edit: I've managed to order the results by the most recent commented statuses, however, if a status has 9 comments, the status is shown 9 times. Any advice why/how this happens?

edit 2: I probably should use ->distinct(), I've added it after the db select statement, but  this doesn't work (probably because I select two tables)
You have a classical case, that requires using 'HAVING' SQL operator. Suppose, we have 2 related tables:

| table1 |
|--------|
|   id   |
|--------|
|    1   |
|    2   |
|--------|


table2.f_id is the FK for table1.id

|         table2        |
|--------+------+-------|
|     id | f_id | order |
|--------+------+-------|
|      1 |    1 |     1 |
|      2 |    1 |     2 |
|      3 |    2 |     3 |
|      4 |    2 |     4 |
|--------+------+-------|


And you want to get all the records from table1 with MAX of the related table2.order. And sort them by table2.order. You must use this SQL to get the data:

SELECT
  table1.id AS id1,
  table2.id AS id2,
  table2.order
FROM
    table1
INNER JOIN
    table2
ON
    table1.id=table2.f_id
GROUP BY
    table1.id
HAVING
    MAX(table2.order)
ORDER BY
    table2.order DESC


And you will get:

| id | id1 | order |
|----+-----+-------|
|  2 |   3 |     3 |
|  1 |   1 |     1 |
|----+-----+-------|


Fiddle

Converting this SQL to Drupal, we'll get this:

$query = db_select("table1");
$query-&gt;innerJoin("table2", NULL, "table1.id=table2.f_id");
$query-&gt;addField("table1", "id", "id1");
$query-&gt;addField("table2", "id", "id2");
$query-&gt;addField("table2", "order", "order_field");

$query-&gt;groupBy("table1.id");
$query-&gt;having("MAX(table2.order)");
$query-&gt;orderBy("table2.order", "DESC");
$result = $query-&gt;execute();
while($row = $result-&gt;fetchAssoc())
{
      var_dump($row);
}


So, for your case, this query must work:

$query = db_select("statuses", "thread");
$query-&gt;innerJoin("fbss_comments", "comment", "comment.sid = thread.sid");
$query-&gt;fields("thread", array("sid", "sender", "created", "message"));
$query-&gt;fields("comment", array("created"));

$query-&gt;groupBy("thread.sid");
$query-&gt;having("MAX(comment.created)");
$query-&gt;orderBy("comment.created", "DESC");
$result = $query-&gt;execute();