Hi all, I greatly need your help with something.
Let's say I have data like the following.
empid classify date rate
10 6136 1/1/2017 20.22
10 6136 3/5/2017 22.45
10 LONG 2/22/2016 1.23
10 LONG 3/12/2017 1.55
20 1241 2/5/2016 10.50
20 1241 4/22/2017 15.64
20 LONG 12/22/2016 2.33
20 LONG 4/24/2017 3.25
I would like a query which would produce two rows per empid. One lists the most recent rate (according to date) where the classify is 'LONG' and the other shows the most recent rate where the classify is not 'LONG' whatever that value may be.
The desired result:
EMPID DATE RATE
10 3/5/2017 22.45
10 3/12/2017 1.55
20 4/22/2017 15.64
20 4/25/2017 3.25
Thanks so much for your help.