CODEWITHSUNDEEP
postgresqlgroupingpostgresql-9.6
sommeguyy
sommeguyy

Reputation: 87

can not order results properly when grouping with distinct on

CREATE  TABLE test(
id integer,
content text,
number integer
)

INSERT  INTO test(id,content,number) VALUES(1,'a'::text, 5);
INSERT  INTO test(id,content,number) VALUES(2,'b'::text, 2);
INSERT  INTO test(id,content,number) VALUES(3,'c'::text, 2);
INSERT  INTO test(id,content,number) VALUES(4,'d'::text, 3);
INSERT  INTO test(id,content,number) VALUES(5,'e'::text, 1);
INSERT  INTO test(id,content,number) VALUES(6,'f'::text, 3);
INSERT  INTO test(id,content,number) VALUES(7,'g'::text, 3);
INSERT  INTO test(id,content,number) VALUES(8,'h'::text, 2);
INSERT  INTO test(id,content,number) VALUES(9,'i'::text, 4);

What i want is, grouping number column and ordering results with id column as desc, like this;

| id | number
----------------
| 9  |    4
| 8  |    2
| 7  |    3
| 5  |    1

Here all numbers with multiple appearance like 2,3 and 1 are grouped and seen only once and also ordered with id column desc.

I have tried this query but it doesn't work for me;

SELECT DISTINCT ON (number) number, id FROM test  ORDER  BY number,id DESC LIMIT 4

Upvotes: 0

Views: 45

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42793

You can also:

select max(id) as id, number   
from test
group by number
order by id desc
limit 4

Upvotes: 1

klin
klin

Reputation: 121754

Use a derived table:

SELECT id, number
FROM (
    SELECT DISTINCT ON (number) number, id 
    FROM test
    ORDER BY number, id DESC
    ) s
ORDER BY id DESC
LIMIT 4;

 id | number 
----+--------
  9 |      4
  8 |      2
  7 |      3
  5 |      1
(4 rows)

Upvotes: 1

Related Questions