Page 2 of 2
Posted: Tue Jun 10, 2003 8:12 am
by ckuipers
Now my English isn't anywhere near perfect, but this is what I read on the mysql site:
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.
workaround
Posted: Wed Jul 09, 2003 4:05 am
by briiii
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
Posted: Wed Jul 09, 2003 8:23 am
by Stoker
Depending a bit on tables, indexes, joins and so forth, I believe in most cases it is better (less resource waste) to use MAX() to get the primary key's first then use another query to get all the data from those records.. (WHERE on the keys)
Use derived tables
Posted: Thu Jul 31, 2003 1:07 pm
by 40winks
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