I need to alter the query below to only return one row for each unique 'name'. The row returned should be the most recent based on the contents of 'date'. 'date' is an integer.
$query = db_select('updates_summary', 'n') ->fields('n', array('upid', 'name', 'date','previous_version','new_version')) ->orderBy('date', 'DESC') //Most recently updated first. ->execute(); return $query;
I have looked at the Drupal page on Expressions, another Drupal answers question and questions on drupal.org like this and this. Unfortunately I am new to the db-api and am struggling to get anywhere.
This works to return a single date but nothing else in the other columns:
$query = db_query("SELECT MAX(upid) as max_id FROM updates_summary")->fetchCol(); return $query; }
This is the mySQL query I need an equivalent of:
SELECT `name`, MAX(date) FROM `updates_summary` GROUP BY `name`
->groupBy('name') to my query seems to do what I need it to. However, i am confused as to why this seems to be working without specifying that I want to return the maxium from the date column. My current code that seems to work is:
$query = db_select('updates_summary', 'n') ->fields('n', array('upid', 'name', 'date','previous_version','new_version')) ->groupBy('name') ->execute();
I would like to add a MAX(date) as I did in the mySQL statement above. This answer suggests that adding the following should work:
->addExpression('MAX(date)', 'max_date') but i get
Fatal error: Call to a member function fields() on a non-object.