SQL – find usercode based on latest timestamp

I have a table that look like this:

I would like to make a query that creates another table called “LastUser” which shows the USER that has the last TIME. In the example above that would be record 2 – 053527/dlmk.

It should look something like this:

TIME     USER   LASTUSER
083527   dph    dlmk
084119   dlmk   dlmk
083527   dph    dlmk
074747   dph    dlmk

​x
 
TIME     USER   LASTUSER083527   dph    dlmk084119   dlmk   dlmk083527   dph    dlmk074747   dph    dlmk​

Any suggestion how I can do this?

Answer

You can use first_value() for this:

select t.*,
       first_value(user) over (order by time desc) as last_user
from t;

 
select t.*,       first_value(user) over (order by time desc) as last_userfrom t;​

Advertisement

Answer