Re: Using MAX function to fetch primary id

[Brent Baisley]

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
  110 1
  220 2
  335 2
  415 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]

Using MAX function to fetch primary id

[Cathy Fusko]

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
  110 1
  220 2
  335 2
  415 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

RE: Using MAX function to fetch primary id

[Daevid Vincent]

 -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
   110 1
   220 2
   335 2
   415 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. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]