RANGE

PostgreSQL

Example

CREATE TABLE range_table (
    id serial PRIMARY KEY,
    value_range int4range
);

INSERT INTO range_table(value_range) VALUES
    ('[1,5]'),('[10,15]'),('[20,25]');

SELECT *
FROM range_table
WHERE value_range @> 11;

Output

 id | value_range
----|-------------
  2 | [10,15]
(1 row)

Explanation

In the above example, a range type int4range is created for storing integer values range in range_table. A few ranges are inserted into the table. The SELECT query with the WHERE condition value_range @> 11 shows the rows where the range includes the number 11. In this case, only the range ‘[10,15]’ includes the number 11, so it returns that row only.

SQL Server

Example

SELECT
    EmployeeID,
    Name,
    Salary,
    NTILE(2) OVER (
    ORDER BY Salary DESC RANGE UNBOUNDED PRECEDING
  ) as SalaryRange
FROM Employees
ORDER BY Salary DESC;

Output

EmployeeID | Name     | Salary | SalaryRange
-----------| -------- | -------| -----------
1          | John     | 9000   | 1
2          | Jane     | 8000   | 1
3          | Alice    | 7000   | 2
4          | Bob      | 6000   | 2

Explanation

In the given example, RANGE UNBOUNDED PRECEDING is used within the OVER clause to divide the employees into two groups based on their salaries. The NTILE(2) function is used to distribute the employees into two groups. The highest salary range is denoted by 1 and the next highest by 2. The RANGE UNBOUNDED PRECEDING clause considers all rows from the start of the partition going back to the row in current processing. Here, it arranges employees in descending order, and divides them into two groups based on their salaries.

Oracle

Example

WITH range_example AS (
  SELECT
    product_name,
    list_price,
    NTILE(4) OVER(
      ORDER BY
        list_price DESC
    ) as price_range
  FROM
    products
)
SELECT
  product_name,
  list_price,
  price_range,
  CASE
    WHEN price_range = 1 THEN 'High'
    WHEN price_range = 2 THEN 'Medium High'
    WHEN price_range = 3 THEN 'Medium Low'
    ELSE 'Low'
  END as price_label
FROM
  range_example;

Output

| product_name    | list_price | price_range | price_label  |
|-----------------|------------|-------------|--------------|
| Product 1       | 300.00     | 1           | High         |
| Product 2       | 250.00     | 1           | High         |
| Product 3       | 200.00     | 2           | Medium High  |
| Product 4       | 150.00     | 2           | Medium High  |
| Product 5       | 100.00     | 3           | Medium Low   |
| Product 6       | 50.00      | 4           | Low          |

Explanation

The SQL code puts products into four groups according to their prices by descending order, using the NTILE function. It then assigns labels to each range with a CASE statement. Price range is defined as:

• 1: High,
• 2: Medium High,
• 3: Medium Low,
• 4: Low.