Different ResultSet in MySQL and MariaDB using GROUP BY and ORDER BY
I have a table: voj_submission
with the following structure:
CREATE TABLE IF NOT EXISTS `voj_submissions` (
`submission_id` bigint(20) NOT NULL,
`problem_id` bigint(20) NOT NULL,
`uid` bigint(20) NOT NULL,
`submission_judge_result` varchar(8) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8;
And the data in the table might look like this:
SubmissionID ProblemID UserID JudgeResult
1000 1000 User1 AC
1001 1000 User1 WA
1002 1000 User2 CE
1003 1001 User1 AC
And the following SQL wants to get the last result of the problem:
SELECT submission_id, problem_id, submission_judge_result
FROM (
SELECT * FROM
voj_submissions
ORDER BY submission_id DESC
) s
WHERE uid = 'User1'
AND problem_id >= 1000
AND problem_id < 1010
GROUP BY problem_id
In MySQL 5.6, it works correctly and returns the following result:
SubmissionID ProblemID JudgeResult
1001 1000 WA
1003 1001 AC
But in MariaDB 10.0.14 it returns:
SubmissionID ProblemID JudgeResult
1000 1000 AC
1003 1001 AC
This means it ORDER BY DESC
didn't run in MariaDB. How can I solve this problem?
Link:
source to share
As @bluefeet said in this post :
Using ORDER BY in a subquery is not the best solution to this problem.
The best solution for getting max (post_date) by the author is to use a subquery to return the max date and then join it to your table in both post_author and max date.
So the solution should be:
SELECT submission_id, s1.problem_id, submission_judge_result
FROM voj_submissions s1
INNER JOIN (
SELECT MAX(submission_id) AS max_submission_id, problem_id
FROM voj_submissions
WHERE uid = 1000
AND problem_id >= 1000
AND problem_id < 1010
GROUP BY problem_id
) s2
ON s1.problem_id = s2.problem_id
AND s1.submission_id = s2.max_submission_id
And it works in both MariaDB and MySQL.
source to share