Hi,
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 ?

cathy
www.nachofoto.com

> -----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
" with subqueries... MAX() and GROUP BY"

Also these links may .

http://dev.mysql.com/doc/refman/4.1/...group-row.html

http://www.xaprb.com/blog/2006/12/07...astmax-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.

You can do it as long as there is only a single record with the max
value. If there is more than 1 record with the same max value, there
isn't a single record to pull.

To do it, you would need to join on the results of the max query, and
part of the join condition would be the max value

SELECT id, count, cat_id FROM table_x JOIN
( SELECT cat_id, max(count) maxcount
FROM table_x
GROUP BY cat_id ) AS table_max
ON table_x.cat_id=table_max.cat_id AND table_x.count=table_max.maxcount

I'm not sure if I got the syntax perfect, but that the concept of how
you would do it.


On Sep 7, 2007, at 3:11 PM, Cathy Fusko wrote:

> Hi,
> 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 ?
>
> cathy
> www.nachofoto.com