Egor Egorov <[EMAIL PROTECTED]> wrote: > Duncan Hill <[EMAIL PROTECTED]> wrote: >> I have a table that has data that looks like: >> +----+---------------------+-------+---------+ >> | id | recdate | mount | perused | >> +----+---------------------+-------+---------+ >> | 1 | 2004-05-20 10:46:12 | QUAR | 80 | >> | 2 | 2004-05-20 11:05:11 | QUAR | 99 | >> | 2 | 2004-05-20 11:33:27 | ROOT | 99 | >> | 2 | 2004-05-20 11:33:34 | QUAR | 88 | >> +----+---------------------+-------+---------+ >> >> I'm trying to work out a query that would list the latest entry per id, per >> mount, and not having much luck. In this case, the result would be >> 1,QUAR,80 >> 2,ROOT,99 >> 2,QUAR,88 >> >> (there are a few other fields to be displayed, but I'll take a base query and >> adapt). >> >> Can anyone give me a shove in the right direction ? >> > > From version 4.1 subquery helps you to get result using one query. > In earlier version you can use temporary table, for example: > > CREATE TEMPORARY TABLE tmp SELECT id, MAX(recdate) as recdate, mount FROM tbl GROUP > BY id, mount; > SELECT tbl.id, tbl.mount, tbl.perused FROM tbl, tmp > WHERE tbl.id=tmp.id AND tbl.recdate=tmp.recdate; >
Oops! Forgot one condition in the WHERE clause: SELECT tbl.id, tbl.mount, tbl.perused FROM tbl, tmp WHERE tbl.id=tmp.id AND tbl.mount=tmp.mount AND tbl.recdate=tmp.recdate; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
