Yes, I'm using 4.1.x and didn't think of doing the query that way. You have been extremely helpfull
Thanks! On Tue, 2005-04-12 at 21:44, Rhino wrote: > 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" <[EMAIL PROTECTED]> > To: "MySql List" <mysql@lists.mysql.com> > 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/[EMAIL PROTECTED] > > > > > > -- > > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
