MySQL Order by before Group by

In a custom module, I needed to get the latest node that each user published. It is fairly easy to goup by users, but it needs a little something to actually accomplish the sorting.

The following query worked for me, we need to do the sorting in a nested select:

pager_query("SELECT * FROM (SELECT * FROM node order by created DESC) as node WHERE nid IN ($placeholders)  GROUP BY uid",10,0,$count_query);

In case the following error comes up:

Error: 1248 - SQLSTATE: 42000 ER_DERIVED_MUST_HAVE_ALIAS

make sure to add the alias just after the FROM clause, outside the parentheses (). In the above example, this is the as node. It might also concern the proper use of JOIN condition.

Recent Comments

Forward by Dries Buytaert, Founder and Project Lead, Drupal; CTO Acquia.

Order now from:

Managing Expectations Podcast with DrupalEasy

Author Interview http://tinyurl.com/8a8nxno

We provide the most Shiny Drupal Bits & Pixels

Since Drupal 4.x