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;
Some info you can also find in the manual at:
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html
--
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]
