Select Unique Fields with Max Values in Each?

Question

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is.

Addy (text), NW (number), UDate (timestamp), Stance (text).

I want to pull only one instance of Addy, and I want the one I pull to have the most recent UDate value. I'd like to make this into a separate table so that I've got the original for historical information and a more usable table with the most recent intel.

I'm using Microsoft Access 2013.

Answer 1

This type of queries are sometimes called "greatest n per group" (there is even a tag in StackOverflow and DBA.SE). In this question, you want one row per group.

A common solution is to use window functions (but these ar enot available in Access) and another is to use GROUP BY in a derived table and then join back to the original table. Like this:

SELECT t.*
FROM 
    TableName AS t
  INNER JOIN
    ( SELECT Addy, MAX(UDate) AS UDate
      FROM TableName
      GROUP BY Addy
    ) AS grp
  ON  grp.Addy = t.Addy
  AND grp.UDate = t.UDate ;