Any way to do DISTINCT ON? I'm trying to create a query like this:

SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM table1


But can't figure out how to get DISTINCT ON narrowed down to a few fields in Drupal like that. Calling $query-&gt;distinct() just seems to structure the query like this:

SELECT DISTINCT field1, field2, field3 FROM table1


I did figure out a hacky way to add what I want by adding an expression before I add any fields like this:

$query-&gt;addExpression('DISTINCT ON (field1, field2) field1', 'field1');


Which creates:

SELECT DISTINCT ON (field1, field2) field1 as field1, field2, field3 FROM table1


I'm looking for a better way to this ?
You could try with the distinct() method for dynamic queries and see how it goes with your query.

There is an example to follow on the taxonomy module of the Drupal core, on the following line:

$node_types = db_select($revision_name)
-&gt;distinct()
-&gt;fields($revision_name, array('bundle'))
-&gt;execute()
-&gt;fetchCol();