This post is intended for developers and data engineers new to ClickHouse or transitioning from traditional RDBMS systems. It provides a practical and beginner-friendly introduction to querying data using the SELECT statement in ClickHouse. It also covers various clauses like LIMIT, DISTINCT, WHERE, GROUP BY, ORDER BY, etc., using real-world examples from a sample e-commerce dataset.
To demonstrate the examples, I am going to use an orders table. Orders table schema looks like below.
|
Column Name |
Data Type |
Description |
|
order_id |
UInt32 |
Unique ID for the order |
|
customer_id |
UInt32 |
ID of the customer |
|
product_name |
String |
Name of the product |
|
quantity |
UInt8 |
Quantity ordered |
|
price |
Float32 |
Price per item |
|
order_date |
Date |
Date of the order |
|
status |
String |
Status like 'Pending', 'Shipped', 'Cancelled' |
1. Setting up orders data
Step 1: Create ecommerce database.
CREATE DATABASE IF NOT EXISTS ecommerce;
krishna :) CREATE DATABASE IF NOT EXISTS ecommerce; CREATE DATABASE IF NOT EXISTS ecommerce Query id: d18a44cc-aa2f-4fe9-b7e7-9b59acbe1e9b Ok. 0 rows in set. Elapsed: 0.003 sec.
Step 2: Create orders table.
CREATE TABLE IF NOT EXISTS ecommerce.orders ( order_id UInt32, customer_id UInt32, product_name String, quantity UInt8, price Float32, order_date Date, status String ) ENGINE = MergeTree ORDER BY (order_date, order_id);
krishna :) CREATE TABLE IF NOT EXISTS ecommerce.orders ( order_id UInt32, customer_id UInt32, product_name String, quantity UInt8, price Float32, order_date Date, status String ) ENGINE = MergeTree ORDER BY (order_date, order_id); CREATE TABLE IF NOT EXISTS ecommerce.orders ( `order_id` UInt32, `customer_id` UInt32, `product_name` String, `quantity` UInt8, `price` Float32, `order_date` Date, `status` String ) ENGINE = MergeTree ORDER BY (order_date, order_id) Query id: bad4720a-0fce-4b44-ab02-ed7e2140dc64 Ok. 0 rows in set. Elapsed: 0.009 sec.
Step 3: Insert Data into orders table.
INSERT INTO ecommerce.orders VALUES (1, 101, 'Laptop', 1, 1200.00, '2025-05-01', 'Shipped'), (2, 102, 'Mouse', 2, 25.00, '2025-05-02', 'Pending'), (3, 103, 'Keyboard', 1, 75.00, '2025-05-03', 'Shipped'), (4, 101, 'Laptop', 1, 1150.00, '2025-05-05', 'Cancelled'), (5, 104, 'Monitor', 2, 200.00, '2025-05-06', 'Shipped'), (6, 105, 'Mouse', 1, 20.00, '2025-05-06', 'Shipped'), (7, 102, 'Keyboard', 2, 70.00, '2025-05-07', 'Pending'), (8, 106, 'Laptop', 1, 1250.00, '2025-05-08', 'Shipped'), (9, 104, 'Monitor', 1, 190.00, '2025-05-08', 'Cancelled'), (10, 107, 'Webcam', 3, 50.00, '2025-05-09', 'Shipped'), (11, 101, 'Mouse', 4, 22.00, '2025-05-10', 'Pending'), (12, 103, 'Monitor', 1, 210.00, '2025-05-11', 'Shipped'), (13, 105, 'Laptop', 2, 1180.00, '2025-05-12', 'Shipped'), (14, 108, 'Webcam', 1, 55.00, '2025-05-12', 'Pending'), (15, 106, 'Keyboard', 1, 80.00, '2025-05-13', 'Cancelled');
krishna :) INSERT INTO ecommerce.orders VALUES (1, 101, 'Laptop', 1, 1200.00, '2025-05-01', 'Shipped'), (2, 102, 'Mouse', 2, 25.00, '2025-05-02', 'Pending'), (3, 103, 'Keyboard', 1, 75.00, '2025-05-03', 'Shipped'), (4, 101, 'Laptop', 1, 1150.00, '2025-05-05', 'Cancelled'), (5, 104, 'Monitor', 2, 200.00, '2025-05-06', 'Shipped'), (6, 105, 'Mouse', 1, 20.00, '2025-05-06', 'Shipped'), (7, 102, 'Keyboard', 2, 70.00, '2025-05-07', 'Pending'), (8, 106, 'Laptop', 1, 1250.00, '2025-05-08', 'Shipped'), (9, 104, 'Monitor', 1, 190.00, '2025-05-08', 'Cancelled'), (10, 107, 'Webcam', 3, 50.00, '2025-05-09', 'Shipped'), (11, 101, 'Mouse', 4, 22.00, '2025-05-10', 'Pending'), (12, 103, 'Monitor', 1, 210.00, '2025-05-11', 'Shipped'), (13, 105, 'Laptop', 2, 1180.00, '2025-05-12', 'Shipped'), (14, 108, 'Webcam', 1, 55.00, '2025-05-12', 'Pending'), (15, 106, 'Keyboard', 1, 80.00, '2025-05-13', 'Cancelled'); INSERT INTO ecommerce.orders FORMAT Values Query id: d9781f07-e266-4d68-bf51-46234886aabd Ok. 15 rows in set. Elapsed: 0.008 sec.
2. SELECT statement explained
The SELECT statement in ClickHouse is used to retrieve data from a table. It is part of DQL (Data Query Language) and is the most commonly used query in any database.
Basic Syntax
SELECT column1, column2, ... FROM database.table;
Example
SELECT order_id, customer_id, product_name FROM ecommerce.orders;
Above statement print the order_id, customer_id and product_name details from orders table.
krishna :) SELECT order_id, customer_id, product_name FROM ecommerce.orders; SELECT order_id, customer_id, product_name FROM ecommerce.orders Query id: f3b8ddb4-924c-4218-a560-e84e9e459b8f ┌─order_id─┬─customer_id─┬─product_name─┐ 1. │ 1 │ 101 │ Laptop │ 2. │ 2 │ 102 │ Mouse │ 3. │ 3 │ 103 │ Keyboard │ 4. │ 4 │ 101 │ Laptop │ 5. │ 5 │ 104 │ Monitor │ 6. │ 6 │ 105 │ Mouse │ 7. │ 7 │ 102 │ Keyboard │ 8. │ 8 │ 106 │ Laptop │ 9. │ 9 │ 104 │ Monitor │ 10. │ 10 │ 107 │ Webcam │ 11. │ 11 │ 101 │ Mouse │ 12. │ 12 │ 103 │ Monitor │ 13. │ 13 │ 105 │ Laptop │ 14. │ 14 │ 108 │ Webcam │ 15. │ 15 │ 106 │ Keyboard │ └──────────┴─────────────┴──────────────┘ 15 rows in set. Elapsed: 0.003 sec.
If you want to select all columns, you can use:
SELECT * FROM database.table;
krishna :) SELECT * FROM ecommerce.orders; SELECT * FROM ecommerce.orders Query id: e51718e2-5cb7-42e1-9b5f-7ba0474369c8 Connecting to localhost:9000 as user default. Connected to ClickHouse server version 25.5.1. ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 3. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 4. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 5. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 6. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 7. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 8. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 9. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 10. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 11. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 12. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 13. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ 14. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 15. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 15 rows in set. Elapsed: 0.006 sec.
2.1 Select constant values for testing
Sometimes, you might want to select fixed (constant) values rather than pull data from a table. This is useful for:
· Testing if your ClickHouse setup works
· Debugging or checking expressions
· Returning metadata or calculated values
Syntax
SELECT <constant1>, <constant2>, ..., <expression>;
These constants can be:
· Numbers (1, 3.14)
· Strings ('hello')
· Booleans (true, false)
· NULLs
· Expressions (1 + 2, 'Hello ' || 'World')
Simple Constants
SELECT 1, 'Hello ClickHouse';
krishna :) SELECT 1, 'Hello ClickHouse'; SELECT 1, 'Hello ClickHouse' Query id: c0a884bd-7863-4bf5-a3a3-5daf4397d0bd ┌─1─┬─'Hello ClickHouse'─┐ 1. │ 1 │ Hello ClickHouse │ └───┴────────────────────┘ 1 row in set. Elapsed: 0.002 sec.
Mathematical Expressions
SELECT 10 + 5, 2 * 3;
krishna :) SELECT 10 + 5, 2 * 3; SELECT 10 + 5, 2 * 3 Query id: c6cfa990-4454-44c5-a981-3b8ed83c8cc1 ┌─plus(10, 5)─┬─multiply(2, 3)─┐ 1. │ 15 │ 6 │ └─────────────┴────────────────┘ 1 row in set. Elapsed: 0.003 sec.
String Concatenation
SELECT 'Hello, ' || 'World!';
krishna :) SELECT 'Hello, ' || 'World!'; SELECT concat('Hello, ', 'World!') Query id: 313e7802-bac0-4fba-b883-2e57ce4951ab ┌─concat('Hello, ', 'World!')─┐ 1. │ Hello, World! │ └─────────────────────────────┘ 1 row in set. Elapsed: 0.003 sec.
In ClickHouse, string concatenation can also be done using concat() function.
SELECT concat('Click', 'House');
krishna :) SELECT concat('Click', 'House'); SELECT concat('Click', 'House') Query id: 4532d62d-b3ed-48e3-b4b6-9c0031ad9da7 ┌─concat('Click', 'House')─┐ 1. │ ClickHouse │ └──────────────────────────┘ 1 row in set. Elapsed: 0.002 sec.
Current Date and Time
SELECT today(), now();
krishna :) SELECT today(), now(); SELECT today(), now() Query id: 605089c7-c36c-473d-83bf-f1253b17f1f0 ┌────today()─┬───────────────now()─┐ 1. │ 2025-05-08 │ 2025-05-08 20:02:15 │ └────────────┴─────────────────────┘ 1 row in set. Elapsed: 0.001 sec.
Using Aliases
You can label (alias) the constants for readability.
SELECT 1 AS id, 'test' AS message;
krishna :) SELECT 1 AS id, 'test' AS message; SELECT 1 AS id, 'test' AS message Query id: 82ba37a6-d0a3-416b-89d3-4c93435b0704 ┌─id─┬─message─┐ 1. │ 1 │ test │ └────┴─────────┘ 1 row in set. Elapsed: 0.002 sec.
NULL and Boolean Values
SELECT NULL AS nothing, true AS is_active;
krishna :) SELECT NULL AS nothing, true AS is_active; SELECT NULL AS nothing, true AS is_active Query id: 3de11d46-1656-4aef-8d64-0b0c395e2c12 ┌─nothing─┬─is_active─┐ 1. │ ᴺᵁᴸᴸ │ true │ └─────────┴───────────┘ 1 row in set. Elapsed: 0.001 sec.
2.3 Select only distinct or unique values
The DISTINCT keyword in a SELECT query is used to return only unique (non-duplicate) values from one or more columns in the result set. It's commonly used when you want to eliminate repeated data and see just the unique combinations of column values.
Syntax
SELECT DISTINCT column1, column2, ... FROM database_name.table_name;
When DISTINCT is applied, ClickHouse (or any SQL engine) examines the values in the specified column(s) and ensures that each returned row is unique based on those column(s).
Get all unique product names
SELECT DISTINCT product_name FROM ecommerce.orders;
krishna :) SELECT DISTINCT product_name FROM ecommerce.orders; SELECT DISTINCT product_name FROM ecommerce.orders Query id: f74b8e72-13e2-4351-894c-9d552232ca4c ┌─product_name─┐ 1. │ Laptop │ 2. │ Mouse │ 3. │ Keyboard │ 4. │ Monitor │ 5. │ Webcam │ └──────────────┘ 5 rows in set. Elapsed: 0.003 sec.
Select unique product_name and quantity from orders table
SELECT DISTINCT product_name, quantity FROM ecommerce.orders;
krishna :) SELECT DISTINCT product_name, quantity FROM ecommerce.orders; SELECT DISTINCT product_name, quantity FROM ecommerce.orders Query id: 38aa1c5f-ba4b-4cec-ba1a-0ff543056ad5 ┌─product_name─┬─quantity─┐ 1. │ Laptop │ 1 │ 2. │ Mouse │ 2 │ 3. │ Keyboard │ 1 │ 4. │ Monitor │ 2 │ 5. │ Mouse │ 1 │ 6. │ Keyboard │ 2 │ 7. │ Monitor │ 1 │ 8. │ Webcam │ 3 │ 9. │ Mouse │ 4 │ 10. │ Laptop │ 2 │ 11. │ Webcam │ 1 │ └──────────────┴──────────┘ 11 rows in set. Elapsed: 0.003 sec.
Select distinct order statuses
krishna :) SELECT DISTINCT status FROM ecommerce.orders; SELECT DISTINCT status FROM ecommerce.orders Query id: 8853a03e-ca53-4a69-998b-b0e0b14af407 ┌─status────┐ 1. │ Shipped │ 2. │ Pending │ 3. │ Cancelled │ └───────────┘ 3 rows in set. Elapsed: 0.002 sec.
2.4 WHERE clause to filter the rows
WHERE clause is used in SQL (and supported in ClickHouse) to filter rows in a table based on specific conditions. Only rows that satisfy the condition(s) are included in the output.
Syntax
SELECT column1, column2, ... FROM table_name WHERE condition;
The WHERE clause evaluates each row in the table against the condition provided. If the condition evaluates to TRUE, the row is returned.
Get all orders that are shipped
SELECT * FROM ecommerce.orders WHERE status = 'Shipped';
krishna :) SELECT * FROM ecommerce.orders WHERE status = 'Shipped'; SELECT * FROM ecommerce.orders WHERE status = 'Shipped' Query id: cf396e01-e413-40d0-8a10-9a2d1d629d5a ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status──┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 3. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 4. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 5. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 6. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 7. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 8. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴─────────┘ 8 rows in set. Elapsed: 0.006 sec.
Get all orders placed after May 5, 2025
SELECT * FROM ecommerce.orders WHERE order_date > '2025-05-05';
krishna :) SELECT * FROM ecommerce.orders WHERE order_date > '2025-05-05'; SELECT * FROM ecommerce.orders WHERE order_date > '2025-05-05' Query id: c08fadcb-012f-4da8-8d36-fbb612237b30 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 2. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 3. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 4. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 5. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 6. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 7. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 8. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 9. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ 10. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 11. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 11 rows in set. Elapsed: 0.007 sec.
Get all orders for product 'Mouse'
SELECT * FROM ecommerce.orders WHERE product_name = 'Mouse';
krishna :) SELECT * FROM ecommerce.orders WHERE product_name = 'Mouse'; SELECT * FROM ecommerce.orders WHERE product_name = 'Mouse' Query id: 86a64a48-1192-4d5e-a775-dc37a3e95266 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status──┐ 1. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 2. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 3. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴─────────┘ 3 rows in set. Elapsed: 0.005 sec.
Get all orders with price between 50 and 200
SELECT * FROM ecommerce.orders WHERE price BETWEEN 50 AND 200;
krishna :) SELECT * FROM ecommerce.orders WHERE price BETWEEN 50 AND 200; SELECT * FROM ecommerce.orders WHERE (price >= 50) AND (price <= 200) Query id: 95906308-ceae-43d0-95e4-c90dea5e6b59 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 2. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 3. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 4. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 5. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 6. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 7. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 7 rows in set. Elapsed: 0.010 sec.
We can combine multiple conditions using logical operators like AND, OR, NOT
Get all the orders for the product laptop where the status is Shipped
SELECT * FROM ecommerce.orders WHERE product_name = 'Laptop' AND status = 'Shipped';
krishna :) SELECT * FROM ecommerce.orders WHERE product_name = 'Laptop' AND status = 'Shipped'; SELECT * FROM ecommerce.orders WHERE (product_name = 'Laptop') AND (status = 'Shipped') Query id: 129aa88b-9c53-4d58-b768-c58ebcc849bf ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status──┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 3. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴─────────┘ 3 rows in set. Elapsed: 0.005 sec.
Get the all the orders where the status is not Shipped
SELECT * FROM ecommerce.orders WHERE NOT status = 'Shipped';
krishna :) SELECT * FROM ecommerce.orders WHERE NOT status = 'Shipped'; SELECT * FROM ecommerce.orders WHERE NOT (status = 'Shipped') Query id: 465d6e5a-7174-4b42-8299-c172fd7b4dba ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 2. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 3. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 4. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 5. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 6. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 7. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 7 rows in set. Elapsed: 0.005 sec.
Get all the orders where the status is Cancelled or Pending
SELECT * FROM ecommerce.orders WHERE status = 'Cancelled' OR status = 'Pending';
krishna :) SELECT * FROM ecommerce.orders WHERE status = 'Cancelled' OR status = 'Pending'; SELECT * FROM ecommerce.orders WHERE (status = 'Cancelled') OR (status = 'Pending') Query id: 91cd5562-bd1a-4eed-9cb5-df5c6c7776ec ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 2. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 3. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 4. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 5. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 6. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 7. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 7 rows in set. Elapsed: 0.004 sec.
2.5 Sort the results by one or more columns
The ORDER BY clause is used in a SELECT query to sort the result set by one or more columns, either in ascending (ASC) or descending (DESC) order.
Syntax
SELECT column1, column2, ... FROM table_name [WHERE condition] ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Sort all orders by order date (oldest first)
SELECT * FROM ecommerce.orders ORDER BY order_date;
krishna :) SELECT * FROM ecommerce.orders ORDER BY order_date; SELECT * FROM ecommerce.orders ORDER BY order_date ASC Query id: 3e581913-c480-4d07-aff8-9e64c8c2e7ba ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 3. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 4. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 5. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 6. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 7. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 8. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 9. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 10. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 11. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 12. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 13. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ 14. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 15. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 15 rows in set. Elapsed: 0.005 sec.
Sort by order date in descending order (latest orders first)
SELECT * FROM ecommerce.orders ORDER BY order_date DESC;
krishna :) SELECT * FROM ecommerce.orders ORDER BY order_date DESC; SELECT * FROM ecommerce.orders ORDER BY order_date DESC Query id: d1fceac0-094c-47c0-89fc-1ed934a3e3c6 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ 2. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 3. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ 4. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 5. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 6. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 7. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 8. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 9. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 10. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 11. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 12. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 13. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 14. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 15. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 15 rows in set. Elapsed: 0.003 sec.
Sort by multiple columns (date first, then price)
SELECT * FROM ecommerce.orders ORDER BY order_date, price;
krishna :) SELECT * FROM ecommerce.orders ORDER BY order_date, price; SELECT * FROM ecommerce.orders ORDER BY order_date ASC, price ASC Query id: ba81d849-31cc-4d50-89af-b5152c184503 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 3. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 4. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 5. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 6. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ 7. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 8. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 9. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 10. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ 11. │ 11 │ 101 │ Mouse │ 4 │ 22 │ 2025-05-10 │ Pending │ 12. │ 12 │ 103 │ Monitor │ 1 │ 210 │ 2025-05-11 │ Shipped │ 13. │ 14 │ 108 │ Webcam │ 1 │ 55 │ 2025-05-12 │ Pending │ 14. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ 15. │ 15 │ 106 │ Keyboard │ 1 │ 80 │ 2025-05-13 │ Cancelled │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 15 rows in set. Elapsed: 0.005 sec.
2.6 Restrict number of rows using LIMIT clause
The LIMIT clause restricts the number of rows returned in a result set.
SELECT column1, column2, ... FROM table_name [WHERE condition] [ORDER BY column] LIMIT N [OFFSET M];
· LIMIT N: returns N rows
· LIMIT N OFFSET M: skips M rows and then returns N rows
Return only the first 5 rows
SELECT * FROM ecommerce.orders LIMIT 5;
SELECT * FROM ecommerce.orders LIMIT 5 Query id: f66fefba-ecea-4777-b02e-ffb8efd37a6f ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 2. │ 2 │ 102 │ Mouse │ 2 │ 25 │ 2025-05-02 │ Pending │ 3. │ 3 │ 103 │ Keyboard │ 1 │ 75 │ 2025-05-03 │ Shipped │ 4. │ 4 │ 101 │ Laptop │ 1 │ 1150 │ 2025-05-05 │ Cancelled │ 5. │ 5 │ 104 │ Monitor │ 2 │ 200 │ 2025-05-06 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 5 rows in set. Elapsed: 0.004 sec.
Get top 3 most expensive orders
SELECT * FROM ecommerce.orders ORDER BY price DESC LIMIT 3;
krishna :) SELECT * FROM ecommerce.orders ORDER BY price DESC LIMIT 3; SELECT * FROM ecommerce.orders ORDER BY price DESC LIMIT 3 Query id: e0e16641-ccc8-4b8c-a28c-1d73efffca5b ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status──┐ 1. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 2. │ 1 │ 101 │ Laptop │ 1 │ 1200 │ 2025-05-01 │ Shipped │ 3. │ 13 │ 105 │ Laptop │ 2 │ 1180 │ 2025-05-12 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴─────────┘ 3 rows in set. Elapsed: 0.006 sec.
Skip first 5 and fetch next 5 (pagination)
SELECT * FROM ecommerce.orders ORDER BY order_date LIMIT 5 OFFSET 5;
krishna :) SELECT * FROM ecommerce.orders ORDER BY order_date LIMIT 5 OFFSET 5; SELECT * FROM ecommerce.orders ORDER BY order_date ASC LIMIT 5, 5 Query id: 335a4869-3993-4910-8bf5-891d653a6f98 ┌─order_id─┬─customer_id─┬─product_name─┬─quantity─┬─price─┬─order_date─┬─status────┐ 1. │ 6 │ 105 │ Mouse │ 1 │ 20 │ 2025-05-06 │ Shipped │ 2. │ 7 │ 102 │ Keyboard │ 2 │ 70 │ 2025-05-07 │ Pending │ 3. │ 8 │ 106 │ Laptop │ 1 │ 1250 │ 2025-05-08 │ Shipped │ 4. │ 9 │ 104 │ Monitor │ 1 │ 190 │ 2025-05-08 │ Cancelled │ 5. │ 10 │ 107 │ Webcam │ 3 │ 50 │ 2025-05-09 │ Shipped │ └──────────┴─────────────┴──────────────┴──────────┴───────┴────────────┴───────────┘ 5 rows in set. Elapsed: 0.004 sec.
2.7 Groups rows that have the same values
The GROUP BY clause groups rows that have the same values in specified columns into summary rows (like totals or counts). It is commonly used with aggregate functions such as:
· COUNT(): number of rows
· SUM(): total of a column
· AVG(): average value
· MIN() / MAX(): smallest / largest value
Total number of orders per customer
SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id;
krishna :) SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id; SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id Query id: 6bb4d309-ae5b-42e2-988d-9626f4744a00 ┌─customer_id─┬─total_orders─┐ 1. │ 104 │ 2 │ 2. │ 105 │ 2 │ 3. │ 106 │ 2 │ 4. │ 108 │ 1 │ 5. │ 107 │ 1 │ 6. │ 101 │ 3 │ 7. │ 103 │ 2 │ 8. │ 102 │ 2 │ └─────────────┴──────────────┘ 8 rows in set. Elapsed: 0.007 sec.
Let’s order the rows by total orders.
SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id ORDER BY total_orders DESC;
krishna :) SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id ORDER BY total_orders DESC; SELECT customer_id, COUNT(*) AS total_orders FROM ecommerce.orders GROUP BY customer_id ORDER BY total_orders DESC Query id: 90fc3aa4-a811-4362-8c0d-5590da58a09e ┌─customer_id─┬─total_orders─┐ 1. │ 101 │ 3 │ 2. │ 104 │ 2 │ 3. │ 105 │ 2 │ 4. │ 106 │ 2 │ 5. │ 103 │ 2 │ 6. │ 102 │ 2 │ 7. │ 108 │ 1 │ 8. │ 107 │ 1 │ └─────────────┴──────────────┘ 8 rows in set. Elapsed: 0.002 sec.
Total quantity sold per product
SELECT product_name, SUM(quantity) AS total_quantity FROM ecommerce.orders GROUP BY product_name;
krishna :) SELECT product_name, SUM(quantity) AS total_quantity FROM ecommerce.orders GROUP BY product_name; SELECT product_name, SUM(quantity) AS total_quantity FROM ecommerce.orders GROUP BY product_name Query id: 1258c29b-fba0-4c58-9620-76f792fbc384 ┌─product_name─┬─total_quantity─┐ 1. │ Mouse │ 7 │ 2. │ Keyboard │ 4 │ 3. │ Webcam │ 4 │ 4. │ Laptop │ 5 │ 5. │ Monitor │ 4 │ └──────────────┴────────────────┘ 5 rows in set. Elapsed: 0.009 sec.
Max order price per status
SELECT status, MAX(price) AS highest_price FROM ecommerce.orders GROUP BY status;
krishna :) SELECT status, MAX(price) AS highest_price FROM ecommerce.orders GROUP BY status; SELECT status, MAX(price) AS highest_price FROM ecommerce.orders GROUP BY status Query id: 3d251878-5459-4938-a169-ad646107a114 ┌─status────┬─highest_price─┐ 1. │ Shipped │ 1250 │ 2. │ Pending │ 70 │ 3. │ Cancelled │ 1150 │ └───────────┴───────────────┘ 3 rows in set. Elapsed: 0.006 sec.
In summary, the SELECT statement in ClickHouse is the fundamental building block of data analysis from filtering (WHERE) and grouping (GROUP BY) to sorting (ORDER BY) and limiting results (LIMIT). Its power lies in simplicity and performance, enables you to analyze massive datasets. Whether you're building reports, dashboards, or complex analytics pipelines, mastering SELECT in ClickHouse gives you a solid foundation for high-performance querying at scale.
Previous Next Home
No comments:
Post a Comment