CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Mohit
Mohit

Reputation: 11314

How to get last record?

I have a table with following schema TableReassignment

Id
StepId
FromUserId
ToUserId

Sample Data For TableReassignment: There may or may not be any record for particular step

1 , Step1 , UserA, UserB 
2 , Step1 , UserB, UserC 
3 , Step1 , UserC, UserD 
4 , Step1 , UserD, UserE 
5 , Step1 , UserF, UserG 
6 , Step2 , UserB, UserC 
7 , Step2 , UserC, UserD 
8 , Step2 , UserE, Userf

Also User Table

UserId
UserName

Also StepUserDetails

StepId,
StepUserId

Sample Data For StepUserDetails:

Step1, UserA
Step2, UserB
Step3, UserC
Step4, UserD

My Requirement is, That I want StepUserDetails with existing StepUsers and if they are reassigned then the last reassigned user.

Expected output should be like 

Step1, UserG
Step2, UserF
Step3, UserC
Step4, UserD

Upvotes: 0

Views: 86

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a subquery 

  select ToUserId
  from my_table where Id =(
  select max(Id)
  from my_table 
  where stepId='Step1'
  )

or a join 

  select ToUserID
  from my_table 
  inner Join (
        select StepId, max(Id) my_id
        from my_table 
        group by StepId
  ) t on t.my_id = my_table.Id  and StepId  ='Step1'

and for your updated question 

  select a.StepID, a.ToUserID
  from my_table  a
  inner Join (
        select StepId, max(Id) my_id
        from my_table 
        group by StepId
  ) t on t.my_id = a.Id

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

WITH ReassignmentCTE AS (
   SELECT StepId, ToUserId, 
          ROW_NUMBER() OVER(PARTITION BY stepId ORDER BY Id DESC) AS rn
   FROM Reassignment
)    
SELECT s.StepId, COALESCE(c.ToUserId, s.StepUserId) AS StepUserId
FROM StepUserDetails AS s
LEFT JOIN ReassignmentCTE AS c ON s.StepId = c.StepId AND c.rn = 1

Demo here

Upvotes: 1

Related Questions