CODEWITHSUNDEEP
postgresqlpostgresql-9.3
danleyb2
danleyb2

Reputation: 1078

Postgresql dense ranking to start at 2 if there is an initial tie at 1

So i have a table and a query that ranks the cost of items and doesn't allows ties with position 1, if there is a tie at position 1 the ranking starts at 2.

Here is the schema with a sample data

  CREATE TABLE applications
      (id int, name char(10), cost int);

  INSERT INTO applications
      (id, name, cost)
  VALUES
      (1, 'nfhfjs', 10),
      (2, 'oopdld', 20),
      (3, 'Wedass', 14),
      (4, 'djskck', 22),
      (5, 'laookd', 25),
      (6, 'mfjjf', 25),
      (7, 'vfhgg', 28),
      (8, 'nvopq', 29),
      (9, 'nfhfj', 56),
      (10, 'voapp', 56);

Here is the query

  WITH start_tie AS (
SELECT 
  DENSE_RANK() OVER(ORDER BY cost DESC) cost_rank,
  lead(cost,1) OVER (ORDER BY cost DESC) as next_app_cost
  FROM 
  applications LIMIT 1
)
  SELECT 
  *, 
  DENSE_RANK() OVER(ORDER BY cost DESC) cost_rank,
  (CASE start_tie.cost_rank WHEN start_tie.next_app_cost THEN cost_rank+1 ELSE cost_rank END) AS right_cost_rank
  FROM 
  applications;

my expected result is 

 id  name    cost  cost_rank
 10  voapp    56     2
 9   nfhfj    56     2
 8   nvopq    29     3
 7   vfhgg    28     4
 6   mfjjf    25     5
 5   laookd   25     5
 4   djskck   22     6
 2   oopdld   20     7
 3   Wedass   14     8
 1   nfhfjs   10     9

Please modify the query to achieve the result.

SQL FIDDLE

Upvotes: 1

Views: 1027

Answers (2)

user330315
user330315

Reputation:

All you need to do is to check if the highest cost is the same as the second-highest cost. And if that is the case, add 1 to all rank values:

with start_tie as (
  select case 
           when cost = lead(cost) over (order by cost desc) then 1 
           else 0
         end as tie_offset
  from applications
  order by cost desc
  limit 1
)
select *, 
       dense_rank() over (order by cost desc) + (select tie_offset from start_tie) cost_rank
from applications;

Example: http://rextester.com/EKSLJK65530

If the number of ties defines the offset to be used for the "new" ranking, the offset could be calculated using this:

with start_tie as (
  select count(*) - 1 as tie_offset
  from applications a1
  where cost = (select max(cost) from applications)
)
select *, 
       dense_rank() over(order by cost desc) + (select tie_offset from start_tie) cost_rank
from applications;

Upvotes: 1

cske
cske

Reputation: 2243

No tie at first, means more than one with rank 1

replace r.cost_rank+x.c-1 with r.cost_rank+1 if fixed start at 2 rank to regardless of how many are in tie ranks are

WITH r AS (
    SELECT
       *
      ,DENSE_RANK() OVER(ORDER BY cost DESC) cost_rank
    FROM
      applications
), x as (select count(*) as c from r where cost_rank=1)
SELECT
  r.*, (CASE WHEN 1<x.c THEN r.cost_rank+x.c-1 ELSE r.cost_rank END) as fixed
FROM
  r,x;

Upvotes: 1

Related Questions