Mega Code Archive

Using DISTINCT with ORDER BY

postgres=# postgres=# CREATE TABLE "editions" ( postgres(# "isbn" text NOT NULL, postgres(# "book_id" integer, postgres(# "edition" integer, postgres(# "publisher_id" integer, postgres(# "publication" date, postgres(# "type" character(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into editions values('039480001X', 1608, 1, 59, '1957-03-01', 'h'); INSERT 0 1 postgres=# insert into editions values('0451160916', 7808, 1, 75, '1981-08-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0394800753', 1590, 1, 59, '1949-03-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0590445065', 25908, 1, 150, '1987-03-01', 'p'); INSERT 0 1 postgres=# insert into editions values('0694003611', 1501, 1, 65, '1947-03-04', 'p'); INSERT 0 1 postgres=# insert into editions values('0679803335', 1234, 1, 102, '1922-01-01', 'p'); INSERT 0 1 postgres=# postgres=# select * from editions; isbn | book_id | edition | publisher_id | publication | type ------------+---------+---------+--------------+-------------+------ 039480001X | 1608 | 1 | 59 | 1957-03-01 | h 0451160916 | 7808 | 1 | 75 | 1981-08-01 | p 0394800753 | 1590 | 1 | 59 | 1949-03-01 | p 0590445065 | 25908 | 1 | 150 | 1987-03-01 | p 0694003611 | 1501 | 1 | 65 | 1947-03-04 | p 0679803335 | 1234 | 1 | 102 | 1922-01-01 | p (6 rows) postgres=# postgres=# -- Using DISTINCT with ORDER BY postgres=# postgres=# SELECT DISTINCT ON (edition) postgres-# edition, publication postgres-# FROM editions postgres-# ORDER BY edition ASC, postgres-# publication DESC; edition | publication ---------+------------- 1 | 1987-03-01 (1 row) postgres=# postgres=# postgres=# postgres=# drop table editions; DROP TABLE postgres=# postgres=# postgres=#