CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Nancy
Nancy

Reputation: 21

Window Function in PostgreSQL

I have a with my SELECT statement that I just can't figure out. The query is as follows:

SELECT 
  count(1),
  interaction_type_id 
FROM
  tibrptsassure.d_interaction_sub_type
GROUP BY 
  interaction_type_id
HAVING 
  count(interaction_type_id) > 1 
ORDER BY
 count(interaction_type_id) DESC 
LIMIT 5;

Since my application does not support the use of the LIMIT keyword, I tried changing my query using the rank() function like so:

SELECT
 interaction_type_id, 
 rank() OVER (PARTITION BY interaction_type_id ORDER BY count(interaction_type_id)
 DESC) 
FROM 
 tibrptsassure.d_interaction_sub_type;

However, this way I ended up with the following error message:

ERROR:  column "d_interaction_sub_type.interaction_type_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  interaction_type_id, rank() OVER (PARTITION BY inter...    
                    ^
    ********** Error **********

ERROR: column "d_interaction_sub_type.interaction_type_id" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 9

Is there an equivalent of rownum() in PostgreSQL? (Apart from using the LIMIT keyword to achieve the same result, that is.)

Does anybody have any suggestions for me? Thanks in advance.

Upvotes: 1

Views: 2450

Answers (2)

Nancy
Nancy

Reputation: 21

The problem was in my query, i.e. there was a syntax error. What I needed was the top 5 category_id and top 5 instances of type_id in each category_id and top 5 instances of sub_type_id in each type_id. To achieve this, I changed the query in the following way and finally got the expected output:

SELECT * FROM (
SELECT t1.int_subtype_key, t2.interaction_sub_type_desc, interaction_category_id, 
interaction_type_id, interaction_sub_type_id, count(interaction_sub_type_id) AS
subtype_cnt,
rank()
over (PARTITION BY interaction_category_id, interaction_type_id ORDER BY 
count(interaction_sub_type_id) DESC) AS rank
FROM tibrptsassure.f_cc_call_analysis t1 INNER JOIN
tibrptsassure.d_interaction_sub_type t2 ON t1.int_cat_key = t2.intr_catg_ref_nbr 
AND t1.int_subtype_key = t2.intr_sub_type_ref_nbr INNER JOIN 
tibrptsassure.d_calendar t3 ON t1.interaction_date = t3.calendar_date GROUP BY
t2.interaction_sub_type_desc, t1.int_subtype_key, interaction_category_id, 
interaction_type_id, interaction_sub_type_id) AS sub_type
WHERE rank <= 5;

Thanks to everyone for paying attention and helping me with this.

Upvotes: 1

Tomas Greif
Tomas Greif

Reputation: 22623

Test whether the following works (it is standard postgresql syntax and should work):

with 
t as (
   select 1 as id union all 
   select 1 as id union all    
   select 2 union all 
   select 2 union all    
   select 3) 

select 
   id
from
   t
group by
   id
having 
   count(id) > 1
order by 
   id desc
limit 1

If this works then you have some syntax problem. If this does not work then you have some other issue - maybe the software you are using is constrained in some really strange way.

You can also use row_number(), but it is not very efficient way:

with 
t as (
   select 1 as id union all 
   select 1 as id union all    
   select 2 union all 
   select 2 union all    
   select 3) 

, u as (
   select 
      id,
      count(*)
   from
      t
   group by
      id
   )

, v as (
   select
      *,
      row_number() over(order by id) c
   from
      u
   )

select
   *
from
   v
where
   c < 2

Upvotes: 1

Related Questions