2

I have a SQL Server table with columns like this but with only the Salary_ID column populated:

Name: Salary
Salary_ID, TotalAmount

I have another SQL Server table with this format and all the columns populated:

Name: SalaryImport
Row_ID, Salary_ID, Amount

I am trying to update the Salary table with the maximum row_id for each salary_id. Currently, there are many instances of each Salary_ID in SalaryImport and I only care about the most recent one which is indicated by the higher number row_id.

Any tips on how this can be done? I thought I would run a SQL statement like but it is grouping too much (and I can remove amount from the group because it is in the select!):

select max(Row_ID), Salary_ID, Amount
from SalaryImport e
group by  Row_ID, Amount
David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
AAA
  • 2,388
  • 9
  • 32
  • 47

1 Answers1

7

Use ROW_NUMBER window function

;with cte as
(
select row_number() over(partition by Salary_ID order by Row_ID desc) as rn,*
From yourtable
)
Update S
Set TotalAmount = c.Amount
From Salary s join cte c on s.Salary_ID = c.Salary_ID
Where RN = 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • I downvoted for duplicating an answer that has been posted hundreds of times to a question that has been posted hundreds of times. We should be flagging duplicates rather than answering, IMO. – Tab Alleman Nov 08 '16 at 16:12
  • It's been discussed in Meta. I don't have a link to the question handy, but a lot of people agree with me. – Tab Alleman Nov 08 '16 at 16:57
  • 1
    Then I would say 90 percnt of the questions asked in SO are duplicates. Should I sit and find the duplicate and mark it as duplicate... instead will one as answer.. will be usefull for OP atleast – Pரதீப் Nov 08 '16 at 17:03
  • @prdp I appreciate the reply! I search and didn't find it (clearly wasn't wording the search correctly). I appreciate you answering my question and taking the time to solve a problem that I ran into! Thank you!! – AAA Nov 08 '16 at 19:26