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