I'm stuck searching for a query

[Olivier]

Hi !

I've got a problem trying to figure out a query.

I have a table with following fields:

tool varchar(30)
last_date datetime
user_id mediumint(8)
....

example:

tool last_date

1 mail 2004-09-05 12:11:44
2 mail 2004-09-01 11:33:12
3 agenda 2004-09-06 17:34:55
4 export 2004-09-02 14:33:16
5 agenda 2004-08-30 19:55:32
6 mail 2004-09-07 14:13:12
7 agenda 2004-09-02 09:54:35

Now i'm trying to creat a query that gives me the tools who were last changed.

this should give:

6 mail 2004-09-07 14:13:12
3 agenda 2004-09-06 17:34:55
4 export 2004-09-02 14:33:16

If anyone can help me with this query, it would be greatly appreciated.

Thanx.

[Patrick]

Select * From tblname Order By last_date

Patrick

sc**********@hotmail.com (Olivier) wrote in message news:<ac**************************@posting.google. com>...

Hi !

I've got a problem trying to figure out a query.

I have a table with following fields:

tool varchar(30)
last_date datetime
user_id mediumint(8)
...

example:

tool last_date

1 mail 2004-09-05 12:11:44
2 mail 2004-09-01 11:33:12
3 agenda 2004-09-06 17:34:55
4 export 2004-09-02 14:33:16
5 agenda 2004-08-30 19:55:32
6 mail 2004-09-07 14:13:12
7 agenda 2004-09-02 09:54:35

Now i'm trying to creat a query that gives me the tools who were last changed.

this should give:

6 mail 2004-09-07 14:13:12
3 agenda 2004-09-06 17:34:55
4 export 2004-09-02 14:33:16

If anyone can help me with this query, it would be greatly appreciated.

Thanx.

[Bill Karwin]

> Now i'm trying to creat a query that gives me the tools who were last changed.

This is one of those types of problem queries that folks post regularly
to this newsgroup: how does one output a group-invariant value _and_
row-specific values in one query?

One method is to use subqueries:

select t1.user_id, t1.tool, t1.last_date
from myTable t1 inner join
(select tool, max(last_date) as max_last_date
from myTable group by tool) t2
on t1.tool = t2.tool and t1.last_date = t2.max_last_date

Note that subqueries require MySQL 4.1. This example won't work in
MySQL 4.0 or earlier.

Another method is to fetch all the rows with a simpler query, and
calculate the max date in your application code. For example, in
Perl/DBI this might be:

my $st = $dbh->prepare('select user_id, tool, last_date from myTable
order by last_date');
$st->execute();
my $result_set = $st->fetchall_arrayref({});
my %tool_hash = ();
for my $row (@$result_set)
{
$tool_hash{ $row->{tool} } = {
user_id => $row->{user_id},
last_date => $row->{last_date}
};
}

This overwrites the hash list entry for each tool as it iterates through
the result set, and since the query was ordered by last_date, it should
naturally produce a final %tool_hash containing the most recent values
per tool.

Note these code examples are untested.

Regards,
Bill Karwin