Question 114829 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – sql [postgres] window function how to create column with last project date?

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)|
|  |            |           |
Tags:

2

Answers


  1. 0 
        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;
    Login or Signup to reply.
  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search