PostgresSQL select-CodePudding

Im beginner in sql, so i need help... I have tabe like this: enter image description here

I need that my query bring back service_id: 5 and id:8, 2 rows(or its depends how many client_ids ill put in my where condition), with client_id, service_type_id, service_id, because service_start_date is max on those 2 clients. I have a full list of clients and i need to do it, thanks for the answers.

CodePudding user response：

i used row_number() OVER( PARTITION BY client_id ORDER BY (service_start_date) desc)

it will give rownumber with client_id service_start_date descending

select 
   * 
from (select
        service_id,
        service_name,
        service_start_date,
        service_end_date,
        client_id,
        service_type_id,
        row_number() OVER( PARTITION BY client_id ORDER BY (service_start_date) desc) as ord 
      from servicetable 
      /* add where condition here*/
      order by service_id) as drt 
where ord=1