Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results.
MAX() and GROUP BY
Moderator: General Moderators
workaround
This workaround should work for you if you're using mysql:
SELECT Id, Version, FirstName, LastName FROM people WHERE Id='0' ORDER BY Version DESC LIMIT 1
SELECT Id, Version, FirstName, LastName FROM people WHERE Id='0' ORDER BY Version DESC LIMIT 1
Use derived tables
I had a similar situation where I needed to retrieve a unique list of questions by type with the latest change date and where the effective date must be <= the current date. Here's the stored procedure definition:
CREATE PROCEDURE spSelectQuestionsByType
@question_type int
AS
SELECT question_id, question_num, question_text, question_type
FROM tblQuestions t1
WHERE question_type = @question_type AND effective_date <= GetDate() AND change_date =
( SELECT MAX( change_date )
FROM tblQuestions t2
WHERE t1.question_num = t2.question_num )
ORDER BY question_num
GO
CREATE PROCEDURE spSelectQuestionsByType
@question_type int
AS
SELECT question_id, question_num, question_text, question_type
FROM tblQuestions t1
WHERE question_type = @question_type AND effective_date <= GetDate() AND change_date =
( SELECT MAX( change_date )
FROM tblQuestions t2
WHERE t1.question_num = t2.question_num )
ORDER BY question_num
GO