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
Performing Aggregations Solution – Daily Product Revenue