Most Recent Date by Combinations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Most Recent Date by Combinations

Author

Topic

bwright611
Starting Member

3 Posts

Posted - 2004-12-21 : 10:33:53

I have a question similar to the most recent date... What if I need the most recent date for a certain combination.. such as:

Person DayOfWeek Achievement AchievementDate
Brad Monday Went Out 12/1/04
Brad Tuesday Drove 12/14/04
Brad Monday Walked 12/5/04
Brad Tuesday Talked 12/15/04
Sallie Monday Wrote 11/4/04
Sallie Monday Drove 12/4/04

So the results will be:

Person DayOfWeek Achievement AchievementDate
Brad Monday Walked 12/5/04
Brad Tuesday Talked 12/15/04
Sallie Monday Drove 12/4/04

Thanks much,
BW

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-21 : 10:48:35

which combination? Person/DayOfWeek ? What if the data is like this:

Mon June 12
Tue June 13
Wed Apr 5

you want to see April mixed in which June's data? If the only Wed in the data occured in a different week, your results might not make much sense. Can you clarify a little more?

IN a general sense, to get the latest date for a combo of columns A and B you say:

select A,B, Max(Date) as MAxDate
from YourData
group by a,b

That only returns the maximum date, not the entire row that contains the maximum date. to do that, you simply join the above SQL to your original table:

select t1.*
from YourTable t1
inner join (above Sql) t2
on t1.A = t2.A and t1.B = t2.B

try it out, expermiment, see if it makes sense.

- Jeff

bwright611
Starting Member

3 Posts

Posted - 2004-12-21 : 10:57:53

Yeah, I need it for the combination of the Person with the day of the week, so for each person they will have up to 7 latest notes (one for each day). I'm testing out what you gave me now so hopefully this works!

Thanks for your help...

quote:
Originally posted by jsmith8858

which combination? Person/DayOfWeek ? What if the data is like this:

Mon June 12
Tue June 13
Wed Apr 5

you want to see April mixed in which June's data? If the only Wed in the data occured in a different week, your results might not make much sense. Can you clarify a little more?

IN a general sense, to get the latest date for a combo of columns A and B you say:

select A,B, Max(Date) as MAxDate
from YourData
group by a,b

That only returns the maximum date, not the entire row that contains the maximum date. to do that, you simply join the above SQL to your original table:

select t1.*
from YourTable t1
inner join (above Sql) t2
on t1.A = t2.A and t1.B = t2.B

try it out, expermiment, see if it makes sense.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources