Selecting Max Date for Many Records

I've got a table that's a list of clients waiting in line. Let's say the table has 4 fields:
(obligatory unique id),client name,worker, time

As the client's make their way though the facility, they see different workers and each worker makes a new entry in this table, with the current time, showing when they saw the client. Later on, this data will be used to analyse where clients spend the most time, so we can't overwrite any records.

What I need is a query that gets only the newest entry for each client.

so in a table like this:
bob, worker1, 1pm
bob, worker2, 2pm
jim, worker1, 3pm
jim, worker2, 4pm
jim, worker3, 5pm

The correct query would return only:
bob, worker2, 2pm
jim, worker3, 5pm

I've tried several different things with Max(time), and intersections, but I can't find a good solution. Surely there's a way to put it all in one sql statement.

Anyone have any ideas?
Thanks!

try "group by" on client field and max(time) in "where" clause.

You cannot use aggregate functions in WHERE clauses. Try a subquery:



I realize this is probably is pseudotable, but I'd like to point out a couple of other things. TIME is a reserved word, so it's best not to use it as a column name. Also, I wouldn't recommend storing times as you've indicated here -- you should use a TIME type. If you store them as nAM/PM strings, your MAX() function won't work correctly.

I had a problem with Army MOTEs having multiple dates on a single table and I only wanted to use the most recent one. The subquery you posted worked great! Hooah!