How to select distinct on with different order by Postgresql

Question

Hello i want to query a table and make a select distinct on but with different ordey by and i want this with desc. How can i do this?

CREATE TABLE test_dupl2(id SERIAL, letter_one TEXT, number_int INT, primary key (id));

INSERT INTO test_dupl2(letter_one,number_int) VALUES ('A',1), ('A',2), ('B',1), ('A', 9), ('B', 4);

My query

select letter_one, number_int from
    (SELECT DISTINCT ON (letter_one) letter_one, number_int FROM test_dupl2) as foo
order by foo.number_int desc;

The wrong output:

('A', 1), ('B', 1)

The output i want:

('A', 9), ('B', 4)

Postgresql9.4

klin · Accepted Answer · 2017-05-01 18:48:15Z

1

select distinct on (letter_one) letter_one, number_int 
from test_dupl2
order by 1, 2 desc;

 letter_one | number_int 
------------+------------
 A          |          9
 B          |          4
(2 rows)

answered May 1, 2017 at 18:48

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

1pa Over a year ago

can you explain me this part "order by 1, 2" desc @klin ?

klin Over a year ago

Order by first and second column, abbrev. for order by letter_one, number_int desc.

Collectives™ on Stack Overflow

How to select distinct on with different order by Postgresql

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