Selecting The Correct Records 1

[rdeleon]

I have an activity table with the following structure/data:

ActivityType ActivityDateTime
1 4/1/05 11:00 am
2 4/2/05 5:00 pm
1 4/4/05 4:00 am
1 4/5/05 3:00 pm
2 4/6/05 11:00 am
3 4/6/05 1:00 pm
3 4/7/05 2:00 pm

What I need is the latest record for each ActivityType. ie

1 4/5/05 3:00 pm
2 4/6/05 11:00 am
3 4/7/05 2:00 pm

Any help is appreciated.

Rene'

 

[hmckillop]

select max(activitydatetime), activitytype
from mytable
group by activitytype

"I'm living so far beyond my income that we may almost be said to be living apart

 

[rdeleon]

Thanks hmckillop, but I realize I didn't give you the whole picture. Here is my problem:

ActivityType ActivityDateTime ActivityAction
1 4/1/05 11:00 am A
2 4/2/05 5:00 pm C
1 4/4/05 4:00 am B
1 4/5/05 3:00 pm A
2 4/6/05 11:00 am D
3 4/6/05 1:00 pm A
3 4/7/05 2:00 pm B

What I need is the latest record for each ActivityType/ActivityAction. ie

1 4/5/05 3:00 pm A
2 4/6/05 11:00 am D
3 4/7/05 2:00 pm B

I can't group by activityaction, because I only want the latest date.

Thanks in advance.

Rene'

 

[SQLBill]

If you want the "the latest record for each ActivityType/ActivityAction" then you would be getting this returned:

ActivityType ActivityDateTime ActivityAction

1 4/4/05 4:00 am B
1 4/5/05 3:00 pm A
2 4/2/05 5:00 pm C
2 4/6/05 11:00 am D
3 4/6/05 1:00 pm A
3 4/7/05 2:00 pm B

That's the latest record for each ActivityType and ActivityAction combination.
1B
1A
2C
2D
3A
3B

-SQLBill

Posting advice: FAQ481-4875

 

[rdeleon]

You're right! I want the latest record for the activity type, but I need the activityaction to let me know what happened to the latest activity type.

Rene'

 

[JamesLean]

I think you mean this:

SELECT activitytype, activitydatetime, activityaction
FROM table t
WHERE activitydatetime = (
    SELECT MAX(activitydatetime)
    FROM table
    WHERE activitytype = t.activitytype
  )
ORDER BY activitytype

--James