Implementing a LIMIT equivalent with a Window Function in PostgreSQL?

Question

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.

What does it mean: "My application does not support limit"? Programming language, PostgreSql, ORM? Consider providing a SQLFiddle demo, and formatting your SQL commands as source. — Beryllium
– Beryllium, Commented May 29, 2013 at 8:18
Intelliview Nxt.. The portal which we use to create reports. This doesnt support the limit function. — Nancy
– Nancy, Commented May 29, 2013 at 9:03
But it does support window aggegate functions (over ... partition by ...)? So you can change the query to using aggregate functions, but not using limit? In other words, what can you use in your query? — Beryllium
– Beryllium, Commented May 29, 2013 at 9:33
Huh, this looks like really strange question or application behaviour. There is row_number() function in postgresql: select row_number() over(). — Tomas Greif
– Tomas Greif, Commented May 29, 2013 at 12:12
@TomasGreif, rownumber is not quite the same as rank. Only the latter can provide a correct ranking in the case when two rows share the same rank. — Asclepius
– Asclepius, Commented Feb 20, 2014 at 17:35

Tomas Greif · Accepted Answer · 2013-05-29 12:21:31Z

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

Priidu Neemre · Accepted Answer · 2014-09-14 17:13:17Z

1

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.

edited Sep 14, 2014 at 17:13

Priidu Neemre

3,0992 gold badges43 silver badges44 bronze badges

answered May 30, 2013 at 6:29

Nancy

216 bronze badges

1 Comment

Asclepius Over a year ago

So basically you surrendered to using a subquery, not that I know of any other way.

Collectives™ on Stack Overflow

Implementing a LIMIT equivalent with a Window Function in PostgreSQL?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related