PostgreSQL/Select Query/Order

Материал из SQL эксперт
Перейти к: навигация, поиск

Order by in ascending order

   <source lang="sql">

postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;

id |  name  | salary | start_date |   city    | region

+--------+--------+------------+-----------+--------
 1 | Jason  |  40420 | 1994-02-01 | New York  | W
 2 | Robert |  14420 | 1995-01-02 | Vancouver | N
 3 | Celia  |  24020 | 1996-12-03 | Toronto   | W
 4 | Linda  |  40620 | 1997-11-04 | New York  | N
 5 | David  |  80026 | 1998-10-05 | Vancouver | W
 6 | James  |  70060 | 1999-09-06 | Toronto   | N
 7 | Alison |  90620 | 2000-08-07 | New York  | W
 8 | Chris  |  26020 | 2001-07-08 | Vancouver | N
 9 | Mary   |  60020 | 2002-06-09 | Toronto   | W

(9 rows) postgres=# postgres=# postgres=# SELECT DISTINCT ON (substr) name, substr(name, 1, 1), ascii(name) postgres-# FROM employee postgres-# ORDER BY substr ASC;

 name  | substr | ascii

+--------+-------
Alison | A      |    65
Celia  | C      |    67
David  | D      |    68
Jason  | J      |    74
Linda  | L      |    76
Mary   | M      |    77
Robert | R      |    82

(7 rows) postgres=# postgres=# drop table employee; DROP TABLE postgres=#

      </source>
   
  


Using DISTINCT and ORDER BY together

   <source lang="sql">

postgres=# postgres=# CREATE TABLE employee ( postgres(# ID int, postgres(# name varchar(10), postgres(# salary real, postgres(# start_date date, postgres(# city varchar(10), postgres(# region char(1) postgres(# ); CREATE TABLE postgres=# postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (1, "Jason", 40420, "02/01/94", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (2, "Robert",14420, "01/02/95", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (3, "Celia", 24020, "12/03/96", "Toronto", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (4, "Linda", 40620, "11/04/97", "New York", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (5, "David", 80026, "10/05/98", "Vancouver","W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (6, "James", 70060, "09/06/99", "Toronto", "N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (7, "Alison",90620, "08/07/00", "New York", "W"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (8, "Chris", 26020, "07/08/01", "Vancouver","N"); INSERT 0 1 postgres=# insert into employee (ID, name, salary, start_date, city, region) postgres-# values (9, "Mary", 60020, "06/09/02", "Toronto", "W"); INSERT 0 1 postgres=# postgres=# select * from employee;

id |  name  | salary | start_date |   city    | region

+--------+--------+------------+-----------+--------
 1 | Jason  |  40420 | 1994-02-01 | New York  | W
 2 | Robert |  14420 | 1995-01-02 | Vancouver | N
 3 | Celia  |  24020 | 1996-12-03 | Toronto   | W
 4 | Linda  |  40620 | 1997-11-04 | New York  | N
 5 | David  |  80026 | 1998-10-05 | Vancouver | W
 6 | James  |  70060 | 1999-09-06 | Toronto   | N
 7 | Alison |  90620 | 2000-08-07 | New York  | W
 8 | Chris  |  26020 | 2001-07-08 | Vancouver | N
 9 | Mary   |  60020 | 2002-06-09 | Toronto   | W

(9 rows) postgres=# postgres=# -- Using DISTINCT and ORDER BY together postgres=# postgres=# SELECT DISTINCT city postgres-# FROM employee postgres-# ORDER BY city;

  city

New York
Toronto
Vancouver

(3 rows) postgres=# postgres=# drop table employee; DROP TABLE postgres=# postgres=#

      </source>
   
  


Using DISTINCT with ORDER BY

   <source lang="sql">

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=#

      </source>
   
  


Using ORDER BY

   <source lang="sql">

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("0596000855", 41473, 2, 113, "2001-03-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
0596000855 |   41473 |       2 |          113 | 2001-03-01  | p

(6 rows) postgres=# postgres=# --Using ORDER BY postgres=# postgres=# SELECT isbn, edition, publication postgres-# FROM editions postgres-# ORDER BY publication ASC;

   isbn    | edition | publication

+---------+-------------
0694003611 |       1 | 1947-03-04
0394800753 |       1 | 1949-03-01
039480001X |       1 | 1957-03-01
0451160916 |       1 | 1981-08-01
0590445065 |       1 | 1987-03-01
0596000855 |       2 | 2001-03-01

(6 rows) postgres=# postgres=# postgres=# postgres=# drop table editions; DROP TABLE postgres=# postgres=# postgres=#

      </source>
   
  


Using ORDER BY with multiple expressions

   <source lang="sql">

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 ORDER BY with multiple expressions postgres=# postgres=# SELECT edition, publication postgres-# FROM editions postgres-# ORDER BY edition ASC, postgres-# publication DESC;

edition | publication

+-------------
      1 | 1987-03-01
      1 | 1981-08-01
      1 | 1957-03-01
      1 | 1949-03-01
      1 | 1947-03-04
      1 | 1922-01-01

(6 rows) postgres=# postgres=# postgres=# postgres=# drop table editions; DROP TABLE postgres=# postgres=#

      </source>