Question about GroupBY and Aggregate functions.

Ok I know this is totally a noob question but I'm working with a table
of the following structure
ID (int), FK (int), Date (datetime)
I would like to select the ID & Max(Date) grouped by the FK. I don't
want to group by the ID but I want it included in the result set.
If I do a simple select like
select ID, FK, Max(Date) from tbl group by FK, ID
I get a result set that includes discreet dates for each ID, not the
max date for a given FK having this ID.
for example
ID FK Date
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
w/ the previous sql returns
1 100 1/1/2006
2 100 1/2/2006
3 150 1/1/2006
4 150 1/2/2006
What I would want is something like this
2 100 1/2/2006
4 150 1/2/2006
Thanks in advance.
S*untested*
select t1.ID,t1.FK,t1.Date
from tbl t1
inner join(
select FK, Max(Date)
from tbl
group by FK ) t2(FK,Date) on t2.FK=t1.FK and t2.Date=t1.Date
Note that if there are multiple IDs sharing the same
maximum date, the query will return both IDs.|||Thanks, I think that works, but is there any way to filter duplicate
dates? Perhaps a distinct on the subquery?
Also is there any way to do it without performing a date comparison?
That's what makes this particular solution work, but wouldn't that be
fairly costly from a resource perspective?|||
> Thanks, I think that works, but is there any way to filter duplicate
> dates? Perhaps a distinct on the subquery?
Not sure I understand your question. If your sample expected
results aren't what you really want (because they share
the same date), can you post some more information.

> Also is there any way to do it without performing a date comparison?
> That's what makes this particular solution work, but wouldn't that be
> fairly costly from a resource perspective?
You can't get away from doing some sort of date comparison
here since your requirements are based on the maximum date.