CODEWITHSUNDEEP
sqlpostgresqlsql-order-bydistinctwindow-functions
MehdiB
MehdiB

Reputation: 906

Postgresql group by multiple columns and sort within each group

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?

Upvotes: 2

Views: 2000

Answers (2)

forpas
forpas

Reputation: 164069

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.

Upvotes: 2

Stu
Stu

Reputation: 32579

It looks like you need to implement row numbering in a window, ordering by your desired criteria:

with t as (
select *,
    Row_Number() 
      over(partition by order_id 
        order by amount desc, rating desc, percent desc, customer_id
      ) seq
    from Yourtable
)
select order_id, doc_id
from t
where seq=1

Upvotes: 0

Related Questions