> -----Original Message-----
> I need to select a max value for a group of records and I
> also need the
> primary key for that record.
>
> I am wondering if this can be done with a single query? e.g
>
> Table_x
>
> id count cat_id
> 1 10 1
> 2 20 2
> 3 35 2
> 4 15 1
>
> with
> SELECT id, cat_id, max(count)
> FROM table_x
> GROUP BY cat_id
>
> I would probably get the following result
>
> id cat_id max(count)
> 1 1 15
> 2 2 35
> and what I would like to get is
>
> id cat_id max(count)
> 4 1 15
> 3 2 35
>
> Is there a way to do this with single query ?
This is an old old topic and one that causes more grief than it's worth and
I wish mySQL would just "fix" this bug (IMHO it *is* a bug) so it works as
people EXPECT it to work...
Search the archives for these topics:
"Erroneus column using MAX() and GROUP BY"
and
"Help with subqueries... MAX() and GROUP BY"
Also these links may help.
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per
-group-in-sql/
The short answer is "no", or you have to use sub-selects, which in effect is
not one select and equally slow and painful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
