MAX() and GROUP BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

ckuipers
Forum Commoner
Posts: 61
Joined: Mon Mar 24, 2003 6:10 am

Post 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.
briiii
Forum Newbie
Posts: 1
Joined: Wed Jul 09, 2003 4:05 am

workaround

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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)
40winks
Forum Newbie
Posts: 1
Joined: Thu Jul 31, 2003 1:07 pm

Use derived tables

Post 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
Post Reply