Home > Net >  sql [postgres] window function how to create column with last project date?
sql [postgres] window function how to create column with last project date?

Time:09-03

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)"
id date_project user_id(FK) user.name last_project
3 2022-04-23 1 alma 2022-04-23
1 2022-02-23 1 alma 2022-04-23
2 2022-03-23 2 luna 2022-03-23

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;
  • Related