0

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.

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 ;
  • Corrected the alias (had c instead of t). – ypercubeᵀᴹ Oct 17 '15 at 17:13
  • Thank you :D that fixed it. It didn't like the circular reference for alias UDate but when I changed that, it did exactly what I wanted it to. :) – LitheOhm Oct 18 '15 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.