Postgresql group by multiple columns and sort within each group

Question

I have a table with the following columns:

order_id	amount	rating	percent	customer_id	ref_id	doc_id
1	1000	1	0.6	112	8	5
2	2000	2	0.1	111	8	8
2	3000	3	0.2	110	8	6
3	4000	5	0.1	100	7	7
3	4000	2	0.7	124	7	9
3	5000	4	0.6	143	5	10
4	2000	6	0.4	125	4	11
4	2500	1	0.55	185	4	12
4	1000	4	0.42	168	5	13
4	1200	8	0.8	118	1	14

for each order_id I want to find the doc_id having the highest amount, highest rating, highest percent, lowest customer_id.

for a single order id I can do it like this:

select order_id, doc_id
from orders
where order_id = 1625
order by amount desc nulls last,
         rating desc nulls last, 
         percent desc nulls last,
         customer_id asc
limit 1;

but I haven't been able to make it for all orders. So the output should be something like this:

order_id	doc_id
1	5
2	6
3	10
4	12

I am using Postgresql. Any idea how I should do this?

Answer 1

Use FIRST_VALUE() window function:

SELECT DISTINCT order_id, 
       FIRST_VALUE(doc_id) OVER (
         PARTITION BY order_id 
         ORDER BY amount DESC NULLS LAST, rating DESC NULLS LAST, percent DESC NULLS LAST, customer_id
       ) doc_id
FROM orders;

See the demo.