Select Max(Date) in Select Group statement

[clueless1]

Hi there,

apologies in advance: I have a set of data, 3 columns: id, accountid & created date. id is unique, accountid & created date are not unique.

I need to select id, accountid where the created date is the highest/most recent, for each unique group of id and accountid.

SELECT id,accountid,CreatedDate
FROM [Task workings 3]
WHERE ((id & accountid & CreatedDate) in (select id & accountid & max(CreatedDate) from [Task workings 3] WHERE accountid = "00120000000DpssAAC" GROUP BY  id,accountid,CreatedDate));

I'd like it to be for all accountid's but I've entered ' WHERE accountid = "00120000000DpssAAC" ' for testing purposes. I only need 1 row returned per id and accountid - with the most recent date for those 2 fields? All I get is every single row?

I'm sure its simple

Thank you to anyone that can help

 

[Joe4]

id is unique

for each unique group of id and accountid

It makes no sense to include a unique field in a grouping. If it is unique, it cannot be "grouped" any further (grouping returns each unique combination, but if one of the fields is already unique, every single combination will already also be unique).

Here is what I am guessing you want to do:
Group each record on the accountid field and return the Max Date. Then return the id field associated with each of those accountid/Max Date combinations.

You could do that like this:

SELECT id, accountid, CreatedDate
FROM [Task workings 3]
INNER JOIN
[COLOR=#0000ff](SELECT accountid, max(CreatedDate) as MaxDate
FROM [Task workings 3]
GROUP BY accountid) as Max[/COLOR]
ON [Task workings 3].accountid = Max.accountid
AND [Task workings 3].CreatedDate = Max.MaxDate;

 

[clueless1]

Sincere apologies - 'It makes no sense to include a unique field in a grouping' - you are of course correct. + Thank you so much, you've made my day!!

 

[Joe4]

Sincere apologies - 'It makes no sense to include a unique field in a grouping' - you are of course correct.

You are welcome. No need to apologize! We all lose site of the forest for the trees, from time-to-time.
Just glad we were able to get working for you.