Get the latest unique entries for user ID in Access database

5 pts.
Tags:
Access
Access Database
Access Database Format
Access Tables
I have a database with a table that lists message entries. Each entry has an autonumber field ID, and a UID field that groups messages. My question is, how can I get the latest entry for each UID? Example Table: ID UID Message 1  1     Some Text 2  2     More Text 3  1     Message response to ID=1 4  1     Another response to ID=1 5  2     Response to ID=2 Desired Result: ID UID Message 5  2     Response to ID=2 4  1     Another response to ID=1 Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

An SQL query like this should work (but there are more ways to do it):

<pre>SELECT y.*
FROM your_table y
INNER JOIN
(SELECT uid,MAX(id) AS max_id
FROM your_table
GROUP BY uid) t
ON y.id = t.max_id
and y.uid = t.uid</pre>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • cyberloco
    what about something like: select uid,message, max(id) over (partition by uid) as lastEntry from messages
    40 pointsBadges:
    report
  • carlosdl
    "what about something like: select uid,message, max(id) over (partition by uid) as lastEntry from messages" AFAIK, Access doesn't support that syntax (somebody please correct me if I'm wrong).
    69,175 pointsBadges:
    report
  • cyberloco
    I think your observation is correct Mr Carlos. I apologizes to the ones who tried my query ho ho.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following