CODEWITHSUNDEEP
sqlvbams-accessms-access-2010
Johnny Bones
Johnny Bones

Reputation: 8402

Getting the most recent data from a dataset based on a date field

This seems easy, but I can't get it. Assume this dataset:

ID  SID AddDate
1   123 1/1/2014
2   123 2/3/2015
3   123 1/4/2010
4   124 
5   124 
6   125 2/3/2012
7   126 2/2/2012
8   126 2/2/2011
9   126 2/2/2011

What I need is the most recent AddDate and the associated ID for each SID.

So, my dataset should return IDs 2, 5, 6 and 7

I tried doing a max(AddDate), but it won't give me the proper ID that's associated with it.

My SQL string:

SELECT First(Table1.ID) AS FirstOfID, Table1.SID, Max(Table1.AddDate) AS MaxOfAddDate
FROM Table1
GROUP BY Table1.SID;

Upvotes: 1

Views: 46

Answers (3)

user1854438
user1854438

Reputation: 1842

Try

select SID from table1 
where addDate in (select max(addDate) from Table1)

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You can use a subquery that returns the Maximum add date for each Sid, then you can join back this subquery to the dataset table:

SELECT
  MAX(id)
FROM
  ds INNER JOIN (
    SELECT Sid, Max(AddDate) AS MaxAddDate
    FROM ds
    GROUP BY ds.Sid) mx
  ON ds.Sid = mx.Sid AND (ds.AddDate=mx.MaxAddDate or MaxAddDate IS NULL)
GROUP BY
  ds.Sid

the join still has to succeed if the MaxAddDate is NULL (there's no AddDate), and in case there are multiple ID that matches, it looks like you want the biggest one.

Upvotes: 2

Rahul
Rahul

Reputation: 77856

You can change your query to get the grouping first and then perform a JOIN like

SELECT First(Table1.ID) AS FirstOfID, 
Table1.SID, xx.MaxOfAddDate
FROM Table1 JOIN (
SELECT ID, Max(AddDate) AS MaxOfAddDate
FROM Table1
GROUP BY SID) xx ON Table1.ID = xx.ID;

Upvotes: 1

Related Questions