List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:September 7 2007 7:23pm
Subject:RE: Using MAX function to fetch primary id
View as plain text  
> -----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. 

Thread
Using MAX function to fetch primary idCathy Fusko7 Sep
  • RE: Using MAX function to fetch primary idDaevid Vincent7 Sep
  • Re: Using MAX function to fetch primary idBrent Baisley11 Sep