Sorting Data¶
Let us understand how to sort the data using SQL.
- We typically perform sorting as final step.
- Sorting can be done either by using one field or multiple fields. Sorting by multiple fields is also known as composite sorting.
- We can sort the data either in ascending order or descending order by using column or expression.
- By default, the sorting order is ascending and we can change it to descending by using
DESC. - As part of composite sorting, we can sort the data in ascending order on some fields and descending order on other fields.
- Typical query execution order
FROMWHEREGROUP BYandHAVINGSELECTORDER BY
SELECT order_date, count(1) AS order_count
FROM orders
WHERE order_status IN ('COMPLETE', 'CLOSED')
GROUP BY order_date
HAVING count(1) > 50
ORDER BY order_count DESC
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%%sql
SELECT * FROM orders LIMIT 10
10 rows affected.
Out[3]:
| order_id | order_date | order_customer_id | order_status |
|---|---|---|---|
| 1 | 2013-07-25 00:00:00 | 11599 | CLOSED |
| 2 | 2013-07-25 00:00:00 | 256 | PENDING_PAYMENT |
| 3 | 2013-07-25 00:00:00 | 12111 | COMPLETE |
| 4 | 2013-07-25 00:00:00 | 8827 | CLOSED |
| 5 | 2013-07-25 00:00:00 | 11318 | COMPLETE |
| 6 | 2013-07-25 00:00:00 | 7130 | COMPLETE |
| 7 | 2013-07-25 00:00:00 | 4530 | COMPLETE |
| 8 | 2013-07-25 00:00:00 | 2911 | PROCESSING |
| 9 | 2013-07-25 00:00:00 | 5657 | PENDING_PAYMENT |
| 10 | 2013-07-25 00:00:00 | 5648 | PENDING_PAYMENT |
In [4]:
%%sql
SELECT * FROM orders
ORDER BY order_customer_id
LIMIT 10
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[4]:
| order_id | order_date | order_customer_id | order_status |
|---|---|---|---|
| 22945 | 2013-12-13 00:00:00 | 1 | COMPLETE |
| 33865 | 2014-02-18 00:00:00 | 2 | COMPLETE |
| 67863 | 2013-11-30 00:00:00 | 2 | COMPLETE |
| 15192 | 2013-10-29 00:00:00 | 2 | PENDING_PAYMENT |
| 57963 | 2013-08-02 00:00:00 | 2 | ON_HOLD |
| 56178 | 2014-07-15 00:00:00 | 3 | PENDING |
| 57617 | 2014-07-24 00:00:00 | 3 | COMPLETE |
| 23662 | 2013-12-19 00:00:00 | 3 | COMPLETE |
| 22646 | 2013-12-11 00:00:00 | 3 | COMPLETE |
| 35158 | 2014-02-26 00:00:00 | 3 | COMPLETE |
In [5]:
%%sql
SELECT * FROM orders
ORDER BY order_customer_id ASC
LIMIT 10
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[5]:
| order_id | order_date | order_customer_id | order_status |
|---|---|---|---|
| 22945 | 2013-12-13 00:00:00 | 1 | COMPLETE |
| 33865 | 2014-02-18 00:00:00 | 2 | COMPLETE |
| 67863 | 2013-11-30 00:00:00 | 2 | COMPLETE |
| 15192 | 2013-10-29 00:00:00 | 2 | PENDING_PAYMENT |
| 57963 | 2013-08-02 00:00:00 | 2 | ON_HOLD |
| 56178 | 2014-07-15 00:00:00 | 3 | PENDING |
| 57617 | 2014-07-24 00:00:00 | 3 | COMPLETE |
| 23662 | 2013-12-19 00:00:00 | 3 | COMPLETE |
| 22646 | 2013-12-11 00:00:00 | 3 | COMPLETE |
| 35158 | 2014-02-26 00:00:00 | 3 | COMPLETE |
In [6]:
%%sql
SELECT * FROM orders
ORDER BY order_customer_id,
order_date
LIMIT 10
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[6]:
| order_id | order_date | order_customer_id | order_status |
|---|---|---|---|
| 22945 | 2013-12-13 00:00:00 | 1 | COMPLETE |
| 57963 | 2013-08-02 00:00:00 | 2 | ON_HOLD |
| 15192 | 2013-10-29 00:00:00 | 2 | PENDING_PAYMENT |
| 67863 | 2013-11-30 00:00:00 | 2 | COMPLETE |
| 33865 | 2014-02-18 00:00:00 | 2 | COMPLETE |
| 22646 | 2013-12-11 00:00:00 | 3 | COMPLETE |
| 61453 | 2013-12-14 00:00:00 | 3 | COMPLETE |
| 23662 | 2013-12-19 00:00:00 | 3 | COMPLETE |
| 35158 | 2014-02-26 00:00:00 | 3 | COMPLETE |
| 46399 | 2014-05-09 00:00:00 | 3 | PROCESSING |
In [7]:
%%sql
SELECT * FROM orders
ORDER BY order_customer_id,
order_date DESC
LIMIT 10
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[7]:
| order_id | order_date | order_customer_id | order_status |
|---|---|---|---|
| 22945 | 2013-12-13 00:00:00 | 1 | COMPLETE |
| 33865 | 2014-02-18 00:00:00 | 2 | COMPLETE |
| 67863 | 2013-11-30 00:00:00 | 2 | COMPLETE |
| 15192 | 2013-10-29 00:00:00 | 2 | PENDING_PAYMENT |
| 57963 | 2013-08-02 00:00:00 | 2 | ON_HOLD |
| 57617 | 2014-07-24 00:00:00 | 3 | COMPLETE |
| 56178 | 2014-07-15 00:00:00 | 3 | PENDING |
| 46399 | 2014-05-09 00:00:00 | 3 | PROCESSING |
| 35158 | 2014-02-26 00:00:00 | 3 | COMPLETE |
| 23662 | 2013-12-19 00:00:00 | 3 | COMPLETE |
In [8]:
%%sql
SELECT o.order_date,
oi.order_item_product_id,
round(sum(oi.order_item_subtotal::numeric), 2) AS revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date,
oi.order_item_product_id
ORDER BY o.order_date,
revenue DESC
LIMIT 25
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 25 rows affected.
Out[8]:
| order_date | order_item_product_id | revenue |
|---|---|---|
| 2013-07-25 00:00:00 | 1004 | 5599.72 |
| 2013-07-25 00:00:00 | 191 | 5099.49 |
| 2013-07-25 00:00:00 | 957 | 4499.70 |
| 2013-07-25 00:00:00 | 365 | 3359.44 |
| 2013-07-25 00:00:00 | 1073 | 2999.85 |
| 2013-07-25 00:00:00 | 1014 | 2798.88 |
| 2013-07-25 00:00:00 | 403 | 1949.85 |
| 2013-07-25 00:00:00 | 502 | 1650.00 |
| 2013-07-25 00:00:00 | 627 | 1079.73 |
| 2013-07-25 00:00:00 | 226 | 599.99 |
| 2013-07-25 00:00:00 | 24 | 319.96 |
| 2013-07-25 00:00:00 | 821 | 207.96 |
| 2013-07-25 00:00:00 | 625 | 199.99 |
| 2013-07-25 00:00:00 | 705 | 119.99 |
| 2013-07-25 00:00:00 | 572 | 119.97 |
| 2013-07-25 00:00:00 | 666 | 109.99 |
| 2013-07-25 00:00:00 | 725 | 108.00 |
| 2013-07-25 00:00:00 | 134 | 100.00 |
| 2013-07-25 00:00:00 | 906 | 99.96 |
| 2013-07-25 00:00:00 | 828 | 95.97 |
| 2013-07-25 00:00:00 | 810 | 79.96 |
| 2013-07-25 00:00:00 | 924 | 79.95 |
| 2013-07-25 00:00:00 | 926 | 79.95 |
| 2013-07-25 00:00:00 | 93 | 74.97 |
| 2013-07-25 00:00:00 | 835 | 63.98 |
In [9]:
%%sql
SELECT o.order_date,
oi.order_item_product_id,
round(sum(oi.order_item_subtotal::numeric), 2) AS revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date,
oi.order_item_product_id
HAVING round(sum(oi.order_item_subtotal::numeric), 2) >= 1000
ORDER BY o.order_date,
revenue DESC
LIMIT 10
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[9]:
| order_date | order_item_product_id | revenue |
|---|---|---|
| 2013-07-25 00:00:00 | 1004 | 5599.72 |
| 2013-07-25 00:00:00 | 191 | 5099.49 |
| 2013-07-25 00:00:00 | 957 | 4499.70 |
| 2013-07-25 00:00:00 | 365 | 3359.44 |
| 2013-07-25 00:00:00 | 1073 | 2999.85 |
| 2013-07-25 00:00:00 | 1014 | 2798.88 |
| 2013-07-25 00:00:00 | 403 | 1949.85 |
| 2013-07-25 00:00:00 | 502 | 1650.00 |
| 2013-07-25 00:00:00 | 627 | 1079.73 |
| 2013-07-26 00:00:00 | 1004 | 10799.46 |
In [10]:
%%sql
DROP TABLE IF EXISTS users
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[10]:
[]
In [11]:
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN DEFAULT FALSE,
user_password VARCHAR(200),
user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
user_country VARCHAR(2),
is_active BOOLEAN DEFAULT FALSE,
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[11]:
[]
In [12]:
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_country)
VALUES ('Donald', 'Duck', 'donald@duck.com', 'IN')
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[12]:
[]
In [13]:
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active, user_country)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true, 'US')
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[13]:
[]
In [14]:
%%sql
INSERT INTO users
(user_first_name, user_last_name, user_email_id, user_password, user_role, is_active, user_country)
VALUES
('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true, 'CA'),
('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true, 'FR'),
('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true, 'AU')
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 3 rows affected.
Out[14]:
[]
In [15]:
%%sql
SELECT * FROM users
ORDER BY user_country
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[15]:
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | user_country | is_active | create_ts | last_updated_ts |
|---|---|---|---|---|---|---|---|---|---|---|
| 5 | Addie | Mesias | amesias2@twitpic.com | False | ih7Y69u56 | U | AU | True | 2022-03-13 09:06:11.482277 | 2022-03-13 09:06:11.482277 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | False | h9LAz7p7ub | U | CA | True | 2022-03-13 09:06:11.482277 | 2022-03-13 09:06:11.482277 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | False | oEofndp | U | FR | True | 2022-03-13 09:06:11.482277 | 2022-03-13 09:06:11.482277 |
| 1 | Donald | Duck | donald@duck.com | False | None | U | IN | False | 2022-03-13 09:06:09.044207 | 2022-03-13 09:06:09.044207 |
| 2 | Mickey | Mouse | mickey@mouse.com | False | None | U | US | True | 2022-03-13 09:06:09.886388 | 2022-03-13 09:06:09.886388 |
In [16]:
%%sql
SELECT user_id,
user_first_name,
user_last_name,
user_email_id,
user_country
FROM users
ORDER BY
CASE WHEN user_country = 'US' THEN 0
ELSE 1
END, user_country
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 5 rows affected.
Out[16]:
| user_id | user_first_name | user_last_name | user_email_id | user_country |
|---|---|---|---|---|
| 2 | Mickey | Mouse | mickey@mouse.com | US |
| 5 | Addie | Mesias | amesias2@twitpic.com | AU |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | CA |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | FR |
| 1 | Donald | Duck | donald@duck.com | IN |