Re: How to select the max value

[Rhino]

What version of MySQL are you using?

If you are running 4.1.x or 5.0.x, you should be able to do this subquery to
get the row you want.

select id, col_x, date_col
from table_x
where date_col =
    (select max date_col
    from table_x)

The subquery gets the max (latest) date in the table, then the outer query
finds the row that has that date on it.) If there are several rows with the
same max date, the outer query will return all of them.

Please note that I am running MySQL 4.0.x so I can't test this in MySQL but
it would work in DB2; DB2 and MySQL are very close in most respects.

Rhino

----- Original Message ----- 
From: "Mauricio Pellegrini" <hrrg-inf@stripped>
To: "MySql List" <mysql@stripped>
Sent: Tuesday, April 12, 2005 7:35 PM
Subject: How to select the max value


> Hi,
> I need to select the max value from a set of records but I also need the
> primary key for that record.
>
> The problem is that the record id may not be the same as the record max
> value for the column as in the following example:
>
> Table_x
>
> Id x_col date_col
> 1 1 2005-04-11
> 2 1 2005-03-10
> 3 1 2005-04-12
> 4 1 2001-01-01
>
> with
> SELECT id, x_col, max(date_col)
> FROM table_x
> GROUP BY x_col
>
> I would probably get the following result
>
> Id x_col date_col
> 4 1 2005-04-12
>
> and what I would like to get is
>
> Id x_col date_col
> 3 1 2005-04-12
>
> Is there a way to do that ?
>
> Thanks in advance
> Mauricio
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/04/2005