CODEWITHSUNDEEP
mysql
Scott
Scott

Reputation: 6736

Bizarre MySQL behavior when trying to aggregate results using MAX()

So the following query that I'm using produces 4 rows:

SELECT 
  `DMV`.`id` AS `dmvID`,
  `D`.`id` AS `documentID`,
  `DMV`.`name` AS `documentName`,
  `U`.`username` AS `creatorUserName`,
  `D`.`folder_id` AS `folderID`,
  `D`.`created` AS `createDate`,
  `SL`.`name` AS `statusName`,
  `UM`.`username` AS `modifiedUserName`,
  `DMV`.`version_created` AS `modifiedDate`,
  `UO`.`username` AS `ownerUserName`,
  `DTL`.`name` AS `documentTypeName` 
FROM 
  `document_metadata_version` `DMV`
  LEFT JOIN `documents` `D` 
    ON `DMV`.`document_id` = `D`.`id`
  LEFT JOIN `users` `U` 
    ON `D`.`creator_id` = `U`.`id`
  LEFT JOIN `users` `UM` 
    ON `D`.`modified_user_id` = `UM`.`id`
  LEFT JOIN `users` `UO` 
    ON `D`.`owner_id` = `UO`.`id`
  LEFT JOIN `status_lookup` `SL` 
    ON `D`.`status_id` = `SL`.`id`
  LEFT JOIN `document_types_lookup` `DTL` 
    ON `DMV`.`document_type_id` = `DTL`.`id`
WHERE
    DMV.document_id = 543433
;

For the sake of brevity, I'll omit all the irrelevant fields not related to the question at hand. The results of dmvID and documentTypeName (first and last columns) are:

+--------+------------------+
| dmvID  | documentTypeName |
+--------+------------------+
| 566053 | Default          |
| 566183 | Default          |
| 681391 | ASCO Documents   |
| 681411 | ASCO Documents   |
+--------+------------------+

I'm only interesting in the last row, so naturally, I'll modify the SELECT statement to use MAX() on dmvID:

SELECT 
  MAX(`DMV`.`id`) AS `dmvID`,
# Rest of query unchanged

That returns:

+--------+------------------+
| dmvID  | documentTypeName |
+--------+------------------+
| 681411 | Default          |
+--------+------------------+

Note from above that 681411 is in fact the MAX(), but the associated documentTypeName is not the equivalent value for dmvID 681411, I would assume it's matching it to dmvID 566053. How do I get it to return:

+--------+------------------+
| dmvID  | documentTypeName |
+--------+------------------+
| 681411 | ASCO Documents   |
+--------+------------------+

Upvotes: 0

Views: 49

Answers (1)

Chris Trahey
Chris Trahey

Reputation: 18290

If you only need the single row with the highest value in that column, use ORDER BY and LIMIT 1 (instead of an aggregating query using MAX()).

...
WHERE
DMV.document_id = 543433
ORDER BY dmvID DESC
LIMIT 1;

Upvotes: 3

Related Questions