top (latest) record for each group

[Michael]

Hi

Am trying to grab the top record for each group of
records (by id) ordered by datetime desc. my table looks
like this:

id method code datetime
1 inquire 100 21:56:57
2 buy 102 17:28:54
2 inquire 100 17:28:32
3 inquire 101 15:30:40
3 inquire 101 15:30:08
4 inquire 101 12:22:33
5 buy 102 12:10:31
5 inquire 100 12:10:06
6 inquire 100 08:42:48
7 inquire 101 22:02:22
8 inquire 101 19:28:27
8 inquire 101 19:26:48
9 inquire 101 19:25:26
10 inquire 101 16:07:10
11 inquire 101 15:43:09
11 inquire 101 15:42:53
12 inquire 101 15:27:22
12 inquire 101 15:27:10
13 inquire 100 13:38:51
14 inquire 100 13:02:57
15 buy 102 10:39:43
15 inquire 100 10:39:17
16 inquire 101 10:27:28
16 inquire 101 10:27:00
16 inquire 101 10:26:44

I would like to get the latest record for each id group
based on the datetime to find the distinct method and
code combinations. the query should show me say:

id method code datetime
1 inquire 100 21:56:57
2 buy 102 17:28:54
3 inquire 101 15:30:40
4 inquire 101 12:22:33
5 buy 102 12:10:31
6 inquire 100 08:42:48
7 inquire 101 22:02:22
8 inquire 101 19:28:27
9 inquire 101 19:25:26
10 inquire 101 16:07:10
11 inquire 101 15:43:09
12 inquire 101 15:27:22
13 inquire 100 13:38:51
14 inquire 100 13:02:57
15 buy 102 10:39:43
16 inquire 101 10:27:28

from here I can then count the distinct combinations of
method and codes. Can anyone help please?

Thanks
Michael

 

[Tom Ellison]

Dear Michael:

Your GROUP appears to be ID and the top appears to be the datetime
column.

Start with this:

SELECT id, MAX(datetime) AS MaxDatetime
FROM YourTable
GROUP BY id

Now I believe you want to see the method and code for the row with the
id / datetime given by this query.

Unless you have built a unique index on id / datetime then there is no
guarantee there is only one row for each of the above id / datetime.
I'm not saying this will definitely happen, but it certainly could.
Just because it doesn't happen NOW doesn't mean it won't tomorrow! So
beware. If and when it does happen, you're going to get two rows with
potentially different method / code values for a single id / datetime
value.

So, to finish this:

SELECT id, method, code, datetime
FROM YourTable T
INNER JOIN (SELECT id, MAX(datetime) AS MaxDatetime
FROM YourTable T1 GROUP BY id) X
ON X.id = T.id AND X.MaxDatetime = T.datetime

Does that work for you?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Michael]

Hi Tom

The your suggested query works, thanks!

Michael