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 |
|
nathans
Aged Yak Warrior
938 Posts |
 Posted - 2005-01-10 : 15:30:25
|
| Hey guys,Im trying to find the value on one column based on the max(date) of another column... all in the same table. I have the following, which I dont feel is very efficient. Any ideas on a different method?Thanks in advance!SELECT a1.account, a1.statusFROM table_A a1 INNER JOIN ( SELECT account, status, MAX(event_date) AS event_date FROM table_A GROUP BY account, status) a2 ON a1.account = a2.account and a1.event_date = a2.event_date |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-10 : 15:38:42
|
| If you have everything indexed properly, what you have posted is about as efficient as you can get. Make sure your PK is account/event_date or there is a unique constraint on those two columns, otherwise you might not return unique sets of accounts/status (assuming that is your goal).- Jeff |
 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-01-10 : 16:53:33
|
| Thanks Jeff. |
|
|
|
|
|
|
Finding value of one col based on MAX of another