Reputation: 45
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:
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.
Upvotes: 0
Views: 135
Reputation: 22811
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.
Upvotes: 2