Need Query for Latest Time per ID

I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.

Here's my scenario, the table looks like this (simplified):

ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am

So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:

101, 8am
104, 7am
260, 9am

It would order the IDs ascending, only showing the newest time assigned to that ID in the table.

Thanks in advance for any help :)select id
, time
from daTable as T
where time
= ( select max(time)
from daTable
where id = T.id )
order
by id|||select, id, max(time)
from daTable
group by id
order by id|||Thanks guys - I used the example that ronin gave because it looked simpler, and it works great. :D

Now I have to add to it. I would have posted this last night but I forgot about it. I have another table that associates IDs with NAMEs. So it looks like this:

101, Apple
104, Orange
260, Banana

What I would like to do is replace the ID with the NAME, so my final query result looked like this:

Apple, 8am
Orange, 7am
Banana, 9am|||if you only want to return those two columns, use the query in post #3

if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3|||What, you don't like your own solution?|||if you only want to return those two columns, use the query in post #3

if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3

I think there is a typo, both your statements say use #3. ;) I edited my post, I ended up using #3 - now I have something to add to the query. Then after that I think I'm done with it.|||thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly|||thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly

My table only has those two columns so it works out fine - but in the future I'm sure I will need your example for other larger tables.

Can you help with replacing the ID with the NAME? thanks :)

edit:
Okay I got it. My final query looks like this:

SELECT idTable.name, MAX(timeTable.time)
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
GROUP BY idTable.name
ORDER BY idTable.name

Thanks for all your help. :)|||Okay, now what I want to try is instead of getting the most recent timestamp using MAX - is it possible to get any that are newer than 9 minutes?|||This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
SELECT idTable.name,
timeTable.time
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
WHERE timeTable.time > dateadd(minute, -9, getdate())
ORDER BY idTable.name|||This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
SELECT idTable.name,
timeTable.time
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
WHERE timeTable.time > dateadd(minute, -9, getdate())
ORDER BY idTable.name

Basic stuff to someone that has dealth with it on a semi-regular basis. :P I'm pretty good with Access, but direct sql is still fairly new to me. I took classes is db theory before but still need more time hands-on.

In this scenario is WHERE better than HAVING?|||In this scenario is WHERE better than HAVING?oh yes, oh my goodness, yes

the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right|||oh yes, oh my goodness, yes

the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right

Okay - I used what blindman gave me, that works great. The only reason I asked about HAVING versus WHERE is because I was trying to search google for help/examples on this before asking you guys, and I was finding stuff that preferred HAVING but the examples didn't quite what I needed.

thanks again :) My query is finally complete, I'll let you guys go back to helping others for awhile. ;)|||HAVING is only meant to be used for criteria involving aggregate functions.