ROW_NUMBER() with 2 columns and specific case

Sebastian Halik 0
Viewed 46

I have table:

id doc_id loc_id price 
1  null   13     100
2  12     13     40   
3  12     null   300
4  null   null   150

I have to select from table the lowest price, but in that order:

  1. If there is doc_id and loc_id equals to parameters then select this price (in our example it would be 40)
  2. If there isn't check if there is row with only equal doc_id, if yes then take price
  3. if there isn't check fi there is row with only equal loc_id, if yes then take price
  4. If there are all nulls take price with nulls

The tables are of course bigger (there are few inner joins), but that's how final table should look like. I got suggestion to use ROW_NUMBER() with partition over doc_id and loc_id, but i am not sure how to to this.

row-number sql postgresql
1 Answers

Not sure where parameters should come from. Using a cte to provide a parameters row

with params(p1,p2)
  as ( values (12,0) ) /*sample params*/
select  tbl.*
from tbl, params
where doc_id = p1 and (loc_id = p2 or loc_id is null) 
   or loc_id = p2 and (doc_id = p1 or doc_id is null) 
   or doc_id is null and loc_id is null
order by case doc_id when p1 then 2 else 0 end + case loc_id when p2 then 1 else 0 end DESC, price ASC
limit 1

The ORDER BY clause first prioritizes the results according to the rules.

db-fiddle

userR76q0
Related