Sorting Data

Private: Mastering SQL using Postgresql Writing Basic SQL Queries 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
1. FROM
2. WHERE
3. GROUP BY and HAVING
4. SELECT
5. ORDER 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

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%sql

SELECT * FROM orders
ORDER BY order_customer_id
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%sql

SELECT * FROM orders
ORDER BY order_customer_id ASC
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%sql

SELECT * FROM orders
ORDER BY order_customer_id,
    order_date
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%sql

SELECT * FROM orders
ORDER BY order_customer_id,
    order_date DESC
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%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:***@localhost:5432/itversity_retail_db
25 rows affected.

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	926	79.95
2013-07-25 00:00:00	924	79.95
2013-07-25 00:00:00	93	74.97
2013-07-25 00:00:00	835	63.98

%%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:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%sql

DROP TABLE IF EXISTS users

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%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:***@localhost:5432/itversity_retail_db
Done.

[]

%%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:***@localhost:5432/itversity_retail_db
1 rows affected.

[]

%%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:***@localhost:5432/itversity_retail_db
1 rows affected.

[]

%%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:***@localhost:5432/itversity_retail_db
3 rows affected.

[]

%%sql

SELECT * FROM users
ORDER BY user_country

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

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	2020-11-14 15:40:12.414932	2020-11-14 15:40:12.414932
3	Gordan	Bradock	gbradock0@barnesandnoble.com	False	h9LAz7p7ub	U	CA	True	2020-11-14 15:40:12.414932	2020-11-14 15:40:12.414932
4	Tobe	Lyness	tlyness1@paginegialle.it	False	oEofndp	U	FR	True	2020-11-14 15:40:12.414932	2020-11-14 15:40:12.414932
1	Donald	Duck	donald@duck.com	False	None	U	IN	False	2020-11-14 15:40:10.878908	2020-11-14 15:40:10.878908
2	Mickey	Mouse	mickey@mouse.com	False	None	U	US	True	2020-11-14 15:40:11.683887	2020-11-14 15:40:11.683887

%%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:***@localhost:5432/itversity_retail_db
5 rows affected.

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

Previous Topic

Back to Lesson

Next Topic