Search code examples
oraclepostgresqlgroup-byhavingrownum

Convert rownum from Oracle in Postgres, in "having" clause

I need to convert a query from Oracle SQL to Postgres.

select count(*) from  table1 group by column1 having max(rownum) = 4

If I replace "rownum" with "row_number() over()", I have an error message: "window functions are not allowed in HAVING". Could you help me to get the same result in Postgres, as in Oracle?

Solution

  • The query below will do what your Oracle query is doing. 

    select count(*) from 
(select column1, row_number() over () as x from table1) as t 
group by column1 having max(t.x) = 6;

    However

    Neither oracle not postgres will guarantee the order in which records are read unless you specify an order by clause. So running the query multiple times is going to be inconsistent depending on how the database decides to process the query. Certainly in postgres any updates will change the underlying row order.

    In the example below I've got an extra column of seq which is used to provide a consistent sort.

    CREATE TABLE table1 (column1 int, seq int);
insert into table1 values (0,1),(0,2),(0,3),(1,4),(0,5),(1,6);

    And a revised query which forces the order to be consistent:

    select count(*) from 
(select column1, row_number() over (order by seq) as x from table1) as t 
group by column1 having max(t.x) = 6;