1

I'm helping a friend figure out some SQL code, but can't figure out why it isn't working like it should.

I have come up with many solutions that work, but I want to know why this one isn't working.

Any help would be appreciated.

SELECT test_id,
    question_id
FROM test
WHERE test_id = 6117
GROUP BY test_id
HAVING question_id = MAX(question_id);

He wants to get the max question id for a specific test. I don't really understand why he wanted to do it this way - something along the lines of performance and functions and subquery. So I'm not sure on that part, though it sounds like it will be used with other SQL code.

If you remove the where clause it works for all test ids, but when you try to specify the test id it isn't working.

  • So I added the question_id to the group by, but still don't get the results. Still getting blank values – J. Does Oct 7 '15 at 18:47
  • 3
    This query is not valid SQL. Change mysql settings to ONLY_FULL_GROUP_BY and MySQL will happily recognize (that it is an invalid query) and complain accordingly. Don't let your friend learn useless and invalid syntax. – ypercubeᵀᴹ Oct 7 '15 at 19:43
5

Selected column in a query with GROUP BY can either be one of the "grouping" columns, functionally dependent on those or an aggregate. I covered the functional dependency just yesterday in this answer. question_id does not fulfill any of these so putting it in there leads to "undefined behavior".

To show what really happens I prepared sql fiddle where you can see that the first query does not return anything and second query shows the details of what actually happens - by removing the HAVING and adding some columns to the SELECT I show what values are assigned to which columns and expressions:

+---------+-------------+
| test_id | question_id |
+---------+-------------+
|       6 |          54 |
|       6 |         300 |
+---------+-------------+

SELECT test_id,
    question_id,
    max(question_id),
    group_concat(question_id)
FROM test
WHERE test_id = 6
GROUP BY test_id;

+---------+-------------+------------------+---------------------------+
| test_id | question_id | max(question_id) | group_concat(question_id) |
+---------+-------------+------------------+---------------------------+
|       6 |          54 |              300 | 54,300                    |
+---------+-------------+------------------+---------------------------+

For test_id = 6 we have two rows with question_id of 54 and 300, because of the GROUP BY we can only select one value per column for a given test_id

  • max() is trivial, it selects 300
  • group_concat() is aggregating function which gets all possible values for given column in a group and makes a comma separated list of them
  • question_id is the interesting case - it can show only one value but two exist for the given test_id - so one is picked "randomly" - as we can see, mysql picks the first one it gets - group_concat shows the internal "ordering", but it depends on many things, one of which is index used - thats why removing the WHERE changes the results such way, different index is used and so different number is picked for this column

The same values are very probably used when you write the same columns and expressions to the HAVING clause and as you can see question_id != max(question_id) because 54 != 300.

To solve this task properly some "groupwise-max" solution is needed. It is even covered in the MySQL manual which shows 3 possible solutions - correlated subquery, derived table and left join. Which one is the best depends on specific case - the use of subquery can be faster than the left join for some data and slower for others. From my experience if you have few groups with lot of items then subquery is generally better whereas the left join performs better with many smaller groups, but you can test them all and see for yourself.

Edit: As other answers show, if you select only test_id and question_id, then the max(question_id) and GROUP BY test_id are enough. I added the previous paragraph because you mentioned in some version of the question that your friend has some "problem" with subqueries etc, so I supposed the actual problem is not as simple as this.

1

He just wants to get the max question id for a specific test?

SELECT MAX(question_id)
FROM test
WHERE test_id = 6117;
  • He does, but he doesn't want to do it that way. I already suggested that solution, but thank you for posting it. He wants to do it with having – J. Does Oct 7 '15 at 17:56

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.