HELP with Simple Query using Max()

[bizproducer]

I need help with a query... this is what my data looks like:

ID AccountID Date
1 893999 031104
2 893999 021104
3 893999 021104
4 893999 041104
5 893999 041104
6 708854 121104
7 708854 121104
8 708854 101104

Basically, I want to only get the records with the most recent dates for each account ID, and as you can see each account ID can have multiple dates that are the most recent and the same. I have messed with correlated subqueries, distinct, max and I can't get the dataset returned. This is what I want to get:

ID AccountID Date
4 893999 041104
5 893999 041104
6 708854 121104
7 708854 121104

HELP! I am pulling my hair out over this!

 

[mercwrought]

perhaps

select accountid, max(date)
from table
group by accountid

i think this will work if that date field is a datetime and not an int.

 

[bizproducer]

No, see query is similar to what I tried abd it only returns 2 records. I need to return the max date for each account id and if 2 records with the same account id have the same dates that are both max, then I need those records.

 

[LazyMe]

To get the two most recent dates you would use

TOP 1 WITH TIES ORDER BY date DESC

To get them from each account, you'll probably have to use subqueries and group by and such.....



Greetings,
Rick

 

[swampBoogie]

select accountid, date
from t as q
where date in (
  select max(date)
    from t
   where t.accountid = q.accountid)

 

[bizproducer]

Oh HOLLLA! That worked beautifully! Thanks so much for the great help!

-Scott McGeary

http://www.bizproducer.com