0

I have the following query 

SELECT
  day,
  category_id,
  name,
  qval
  MIN(CONCAT(category_id, qval))
FROM facts
WHERE
  day = CURDATE()
GROUP BY
  category_id

Will the name that gets selected always be the name associated with the MIN record that is identified?

ekad
  • 14,436
  • 26
  • 44
  • 46
OregonTrail
  • 8,594
  • 7
  • 43
  • 58
  • 1
    In a word, no. Depending on exact version, it will either cause an error (selecting a column that isn't part of the group) or pick one arbitrarily. – Niet the Dark Absol Feb 26 '15 at 23:11
  • 1
    NO, please have a look to this post. http://stackoverflow.com/questions/435703/how-to-select-the-most-recent-set-of-dated-records-from-a-mysql-table – Carlos González Feb 26 '15 at 23:13

1 Answers1

2

Absolutely not. There is no reason to expect this to be the case. This is your query:

SELECT day, category_id, name, qval
       MIN(CONCAT(category_id, qval))
FROM facts
WHERE day = CURDATE()
GROUP BY category_id;

Although not identified as such, this syntax is MySQL. You are using a MySQL extension because you have columns in the select that are not arguments to aggregation functions and are not in the group by. These values come from arbitrary rows. Here is some relevant documentation concerning the use of the extension:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

I believe this problem is fixed in MySQL 5.7, because 5.7 detects functional dependency.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786