# `VERSION::6.5.0` `RANGE COLUMNS` Partition - Example

+ One or more columns as partitioning keys
+ The data types of partition columns can be integer, string (**`CHAR`** or **`VARCHAR`**), **`DATE`**, and **`DATETIME`** 
+ Expressions, such as **`YEAR()`**, are not supported

```
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE NOT NULL
)
~!@PARTITION BY RANGE COLUMNS (journalid, joined)@!~
(
 PARTITION `p2023-1` VALUES LESS THAN (1,'2024-01-01 00:00:00'),
 PARTITION `p2024-1` VALUES LESS THAN (1,'2025-01-01 00:00:00'),
 PARTITION `p2023-2` VALUES LESS THAN (2,'2024-01-01 00:00:00'),
 PARTITION `p3024-2` VALUES LESS THAN (2,'2025-01-01 00:00:00'),
 PARTITION `p2023-3` VALUES LESS THAN (3,'2024-01-01 00:00:00'),
 PARTITION `p3024-3` VALUES LESS THAN (3,'2025-01-01 00:00:00')
); 
```

------------------------------------------------------------------------------
One of the variants of the `RANGE` partition is `RANGE COLUMNS` partitioning. You can use one or multiple columns as the partition key, and the data types for partition columns can be integer, string (`CHAR/VARCHAR`), `DATE`, and `DATETIME`.

For example, in the `subscribers` table mentioned earlier, we can partition the data based on different `journalid` values and further partition each `journalid` based on different `joined` times. This makes it much easier to delete expired data for a specific `journalid`. The partitioning can be achieved as follows:

```
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS (journalid, joined)
(
 PARTITION `p2023-1` VALUES LESS THAN (1,'2024-01-01 00:00:00'),
 PARTITION `p2024-1` VALUES LESS THAN (1,'2025-01-01 00:00:00'),
 PARTITION `p2023-2` VALUES LESS THAN (2,'2024-01-01 00:00:00'),
 PARTITION `p3024-2` VALUES LESS THAN (2,'2025-01-01 00:00:00'),
 PARTITION `p2023-3` VALUES LESS THAN (3,'2024-01-01 00:00:00'),
 PARTITION `p3024-3` VALUES LESS THAN (3,'2025-01-01 00:00:00')
);
```

From the above table creation statement, we can see that we have the following partitions:
- `p2023-1` for `[ (min, ''), (1, '2024-01-01 00:00:00') )`,
- `p2024-1` for `[ (1, '2024-01-01 00:00:00'), (2, '2023-01-01 00:00:00') )`,
- `p2023-2` for `[ (2, '2023-01-01 00:00:00'), (2, '2024-01-01 00:00:00') )`,
- `p3024-2` for `[ (2, '2024-01-01 00:00:00'), (3, '2023-01-01 00:00:00') )`,
- `p2023-3` for `[ (3, '2023-01-01 00:00:00'), (3, '2024-01-01 00:00:00') )`,
- `p3024-3` for `[ (3, '2024-01-01 00:00:00'), (3, '2025-01-01 00:00:00') )`

Here, `[,)` represents a left-closed and right-open interval. For example, `[ (2, '2023-01-01 00:00:00'), (2, '2024-01-01 00:00:00') )` indicates data with `journalid 2`, including the timestamp `'2023-01-01 00:00:00'` and later but less than `'2024-01-01 00:00:00'`. It does not include `(2, '2024-01-01 00:00:00')`. When we want to delete the range scoped data on the partition boundary, we can perform partition pruning based on `journalid` and `joined` columns combined.

More detail: `https://docs.pingcap.com/tidb/stable/partitioned-table#range-partitioning`.