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.
| Author |
Topic |
|
davedean
Starting Member
2 Posts |
 Posted - 2004-08-18 : 13:19:57
|
| I have an app in which values of various factors are stored in a table:Date-Time / Factor / Value01/01/2001 10:10.32 / 'F21' / 201/01/2001 11:24.32 / 'F21' / 101/01/2001 11:25.39 / 'Q4' / 2301/01/2001 11:29.12 / 'Q4' / 6501/01/2002 05:32.54 / 'F21' / 3 I need to develop a view/sp to retrieve a recordset with only the most recent values for each unique factor. I have not been able to figure this out in SQL Server 2000.From the above sample table, I am looking for the following result: Date-Time / Factor / Value01/01/2001 11:29.12 / 'Q4' / 6501/01/2002 05:32.54 / 'F21' / 3Any assistance would be greatly (and desperately) appreciated! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-18 : 13:29:03
|
| well since you're desparate :))select t1.*from MyTable t1inner join (select Value, Max(Factor) as Factor from MyTable group by Factor) t2 on (t1.Factor = t2.Factor) and (t1.Value = t2.Value)Go with the flow & have fun! Else fight the flow :) |
 |
|
|
davedean
Starting Member
2 Posts |
Posted - 2004-08-18 : 13:37:46
|
| spirit1Thanks for the prompt reply, your response was 95% of what I needed. This works great:select t1.*from MyTable t1inner join (select Factor, Max([Date]) as RecentDate from MyTable group by Factor) t2 on (t1.Factor = t2.Factor) and (t1.[Date] = t2.RecentDate)(I wanted the most recent record, not the one with the largest value)One of those cases of panic induced brain farts I suppose! I knew the solution would be simple, but sometimes it pays to have another set of eyes!Thanks again!! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-18 : 13:41:55
|
| :)) yeah we all get those... :))Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
|
|
Retrieving top row for each group in Select Query