CODEWITHSUNDEEP
postgresqlrankdense-rank
akitir
akitir

Reputation: 1

PostgreSQL rank only continuous rows on ordered data

So I ran into this interesting problem, which at first seems easy at first with a simple sql window function. But it isn't. Any solutions that use only rank, or dense rank?

Basically I want to assign rank based on petrol price and effective from dates. The table structure and its data is as below.

[1]: https://i.sstatic.net/se6mB.png

Upvotes: 0

Views: 168

Answers (1)

Vivek S.
Vivek S.

Reputation: 21885

If you are expecting an output like this

price dte        rn 
----- ---------- -- 
65.5  2013-06-01 1  
66.3  2014-06-01 1  
66.3  2015-12-01 2  
67    2012-01-01 1  
67    2012-06-01 2  
67    2013-01-01 3  
67    2014-01-01 4  
67    2016-01-01 5

the query should be

select *
      ,row_number() over(partition by price order by dte) rn
from price;

OR

SELECT *
       ,rank() OVER (PARTITION BY price ORDER BY dte) rn
FROM price;

PostgreSQL's Window functions

Upvotes: 2

Related Questions