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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
