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->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->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)
->fields($revision_name, array('bundle'))

I have use this some time, Hope this will help you.

$result = db_query("SELECT node.uid AS node_uid, node.created AS node_created, COUNT(DISTINCT node.title) AS node_title, COUNT(DISTINCT field_data_body.body_value) AS field_data_body_body_value, COUNT(DISTINCT field_data_body.delta) AS field_data_body_delta, COUNT(DISTINCT field_data_body.language) AS field_data_body_language, COUNT(DISTINCT field_data_body.bundle) AS field_data_body_bundle, COUNT(DISTINCT field_data_body.body_summary) AS field_data_body_body_summary, COUNT(DISTINCT field_data_body.body_format) AS field_data_body_body_format
{node} node")->fetchAll(PDO::FETCH_ASSOC);

you should set that field on your query something like

if you want distinct on 2 field


the query will be

SELECT DISTINCT t1.field1 AS field1, t1.field2 AS field2 FROM {table1} t1

and for one field

SELECT DISTINCT t1.field1 AS field1 FROM {table1} t1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.