i'm knew with window function

I have two tables which i join together (One user could have many project). Each Project has their own date (date_project)

I need query all users with thier projects and add a column in the end which contain information about last project date(last project date==ordered by date_project DESC) which i believe must be compute by window function.

How can i achieve this?

Expected result

|id|date_project|user_id(FK)|user.name|last_project|
|  |            |           |         |            |

Table User

|id | name
|   | 

Table Project

|id|date_project|user_id(FK)|
|  |            |           |

CodePudding user response：

select t_project.id
      ,date_project
      ,"user_id(FK)"
      ,name                                                                                   as "user.name"      
      ,first_value(date_project) over (partition by "user_id(FK)" order by date_project desc) as last_project
      
from t_project join t_user on t_user.id = t_project."user_id(FK)"

Fiddle

CodePudding user response：

    select p.*, u.name, 
    first_value(date_project) over (partition by u.id order by date_project desc) 
as last_project_date
    from users u inner join projects p on u.id=p.user_id;