CODEWITHSUNDEEP
postgresql
максим ильин
максим ильин

Reputation: 194

How do I select multiple values ​from a window function?

select timezone('utc'::text,t.assigned_at)::DATE as time, 
       organization_id as org_id,  
       row_number () over (partition by organization_id order by t.assigned_at desc ) as rownumber  
from table t

>               time         org_id rownumber
>                 06.01.2021    13  5
>                 06.01.2021    13  4
>                 06.01.2021    13  3
>                 06.01.2021    13  2
>                 06.01.2021    13  1
>                 06.01.2021    34  3
>                 06.01.2021    34  2
>                 06.01.2021    34  1

I need 34: 1, 2, 3. How to put a search term in a subquery?

select * 
from (
  select timezone('utc'::text,t.assigned_at)::DATE as time,
         organization_id as org_id, 
         row_number () over (partition by organization_id order by t.assigned_at desc ) as rownumber  
  from table t
) z
where z.rownumber = ?????

Upvotes: 0

Views: 611

Answers (2)

wildplasser
wildplasser

Reputation: 44250

I need 34: 1, 2, 3. How to put a search term in a subquery?

Just put a WHERE clause into the subquery:

select * 
from (
  select timezone('utc'::text,t.assigned_at)::DATE as time
         , organization_id as org_id 
         , row_number ()
             over (partition by organization_id order by t.assigned_at desc )
             as rownumber  
  from table t
  WHERE t.organization_id = 34
) z
where z.rownumber < 4
  ;

Upvotes: 1

максим ильин
максим ильин

Reputation: 194

dense_rank () over (partition by timezone('utc'::text, t.assigned_at)::date,order by ds.organization_id desc) as dec
where dec=1

Upvotes: 0

Related Questions