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 |
|
Walms
Starting Member
2 Posts |
 Posted - 2011-03-09 : 09:32:48
|
| Hi i am new to SQl and struggling with something.For a list of around 4000 accounts i need to find the latest date where a payment amendment was made. The data is several years worth and a change to this column can happen more than once. Need it to return Account_Number New_Data Report_dateacc 1 £30 01/03/2010acc 2 £50 05/08/2010etcFor each account there should be one row with the latest date this change was made only. What i thought would be an easy task is proving tricky.sorry if this is basic but help would be appreciated.Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-09 : 09:35:25
|
[code]select Account_Number, max(Report_date)from yourtablegroup by Account_Number[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Walms
Starting Member
2 Posts |
Posted - 2011-03-09 : 09:41:58
|
| Hii need to pull through the column with the data that has changed as well and that creates issues with group by clause.i dont need to know the date just the figure that changed on this date. Has to be most recent date it was changed though. currently i can run it with all dates the change was made but to only bring through the last time this happened gives issues. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-09 : 09:44:42
|
[code]select *from( select *, row_no = row_number() over (partition by Account_Number order by Report_Date desc) from yourtable) dwhere d.row_no = 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
Latest date of change