List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:September 11 2007 2:02am
Subject:Re: Using MAX function to fetch primary id
View as plain text  
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

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