CODEWITHSUNDEEP
sqlsql-server
tts
tts

Reputation: 107

Row with most recent data per user_id

Currently I have this view but I would like to change it such that the most recent employee 'Stritt' gets all the sales cause he made the last one.

   cal_date     id  sales   emp
    2021-01-04  24  0.85    Krebs 
    2021-01-29  24  11.4    Krebs   
    2021-01-31  24  -5.7    Timo
    2021-02-01  24  0.85    Timo    
    2021-02-26  24  1.00    Stritt
    2021-01-30  33  5.33    Alex
    2021-03-10  33  3.45    Alex    
    2021-04-20  33  1.20    Dom

It should look like this:

   cal_date     id  sales   emp
    2021-01-04  24  0.85    Stritt
    2021-01-29  24  11.4    Stritt
    2021-01-31  24  -5.7    Stritt
    2021-02-01  24  0.85    Stritt
    2021-02-26  24  1.00    Stritt
    2021-01-30  33  5.33    Dom
    2021-03-10  33  3.45    Dom 
    2021-04-20  33  1.20    Dom

Upvotes: 1

Views: 47

Answers (1)

MatBailie
MatBailie

Reputation: 86775

You could use a Window function to find the last value in a partition (the id)

SELECT
  cal_date,
  id,
  sales,
  FIRST_VALUE(emp)
    OVER (
      PARTITION BY id
          ORDER BY cal_date DESC,
                   emp      DESC
    )
      AS emp
FROM
  yourTable

In the case that multiple emp's have records for the latest date, the highest emp value is chosen.

Upvotes: 3

Related Questions