CODEWITHSUNDEEP
sqlpostgresqlgroup-by
user3030748
user3030748

Reputation: 11

postgresql group and sort

I need to reorder my query in some parameters with extracted unique field 'article' with the lowest price. And I should sort by other parameters.

Example table:

article         price   id_item   name    partner weight
1000000001      500     1         Cake 1  sony    100
1000000001      1000    2         Cake 2  apple   100
1000000002      500     3         Beer 1  htc     100
1000000002      1000    4         Beer 2  htc     200

I need this result with sort by other parameters(name, price, partner and other...):
by name:

1000000002      500     3         Beer 1 htc  100
1000000001      500     1         Cake 1 sony 100

by name DESC:

1000000001      500     1         Cake 1 sony 100
1000000002      500     3         Beer 1 htc  100

by partner:

1000000002      500     3         Beer 1 htc  100
1000000001      500     1         Cake 1 sony 100

Upvotes: 1

Views: 308

Answers (1)

roman
roman

Reputation: 117345

You don't have to group your data and get min. distinct on will take all records with minimum price within article

select distinct on (article)
    article, price, id_item, name
from rurbox_mod_product.item
order by article, price

sql fiddle demo

if you want to sort resultset, you can use common table expression or subquery and then sort the results:

with cte as (
    select distinct on (article)
        article, price, id_item, name
    from item
    order by article, price
)
select *
from cte
order by name;

select *
from (
    select distinct on (article)
        article, price, id_item, name
    from item
    order by article, price  
) as A
order by name desc;

sql fiddle demo

Upvotes: 1

Related Questions