postgresql group and sort

Question

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

roman · Accepted Answer · 2013-11-25 12:14:18Z

1

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

edited Nov 25, 2013 at 12:14

answered Nov 25, 2013 at 11:11

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noam Rathaus Over a year ago

If you need to find "minimum" value but then sort it using some other parameter, you will need first return the above SQL into a new table (using alias) and then do a select around it that does your other sort

user3030748 Over a year ago

Can you give me a example please?

Collectives™ on Stack Overflow

postgresql group and sort

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related