Databricks SQL: Filter, Sort, and Limit Result Sets

This tutorial demonstrates how to filter, sort, and limit result sets using Databricks SQL. We’ll work through several scripts, each building upon the previous one, focusing on the core concepts. All code is intended to be executed directly within the Databricks SQL query editor.

Script 1: Basic Filtering

This script introduces the fundamental concept of filtering data based on a condition.

-- Create a sample table with customer data.
CREATE TABLE IF NOT EXISTS customers (
    customer_id INT,
    customer_name STRING,
    city STRING,
    age INT
);

-- Insert some sample data.
INSERT INTO customers (customer_id, customer_name, city, age) VALUES
(1, 'Alice', 'New York', 30),
(2, 'Bob', 'London', 25),
(3, 'Charlie', 'Paris', 40),
(4, 'David', 'New York', 35),
(5, 'Eve', 'London', 28);

-- Filter customers who live in New York.
SELECT
    customer_id,
    customer_name,
    city,
    age
FROM
    customers
WHERE
    city = 'New York';

-- Final validation SELECT.
SELECT
    COUNT()
FROM
    customers
WHERE
    city = 'New York';

This script first creates a table named `customers` with columns for `customer_id`, `customer_name`, `city`, and `age`. It then inserts five rows of sample data. The first `SELECT` statement filters the data to include only customers from ‘New York’. The final `SELECT` statement validates the number of records returned after filtering.

Output:

3

Script 2: Sorting and Limiting

This script demonstrates how to sort and limit the results after filtering. The key here is understanding the order of operations.

-- Create a sample table with sales data.
CREATE TABLE IF NOT EXISTS sales (
    sale_id INT,
    customer_id INT,
    sale_date DATE,
    amount DECIMAL
);

-- Insert some sample sales data.
INSERT INTO sales (sale_id, customer_id, sale_date, amount) VALUES
(1, 1, '2023-01-15', 100.00),
(2, 2, '2023-02-20', 150.50),
(3, 1, '2023-03-10', 75.25),
(4, 3, '2023-04-05', 200.00),
(5, 2, '2023-05-12', 125.75);

-- Filter sales with an amount greater than 100 and sort by amount in descending order.
SELECT
    sale_id,
    customer_id,
    sale_date,
    amount
FROM
    sales
WHERE
    amount > 100.00
ORDER BY
    amount DESC
LIMIT 2;

-- Final validation SELECT.
SELECT
    COUNT()
FROM
    sales
WHERE
    amount > 100.00
ORDER BY
    amount DESC;

This script creates a `sales` table. It inserts sample data representing sales transactions. The `SELECT` statement first filters sales where the `amount` is greater than 100.00. Then, it sorts the filtered results in descending order based on the `amount` column using `ORDER BY amount DESC`. Finally, it limits the output to the top 2 rows using `LIMIT 2`. The final SELECT validates the count.

Output:

2

Script 3: Combining Filtering, Sorting, and Limiting

This script combines all three operations to demonstrate a more complex scenario.

-- Create a sample table with product data.
CREATE TABLE IF NOT EXISTS products (
    product_id INT,
    product_name STRING,
    category STRING,
    price DECIMAL
);

-- Insert some sample product data.
INSERT INTO products (product_id, product_name, category, price) VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Tablet', 'Electronics', 300.00),
(3, 'Shirt', 'Clothing', 50.00),
(4, 'Pants', 'Clothing', 75.00),
(5, 'Headphones', 'Electronics', 150.00);

-- Filter products in the 'Electronics' category, sort by price in ascending order, and limit to 3 results.
SELECT
    product_id,
    product_name,
    category,
    price
FROM
    products
WHERE
    category = 'Electronics'
ORDER BY
    price ASC
LIMIT 3;

-- Final validation SELECT.
SELECT
    COUNT()
FROM
    products
WHERE
    category = 'Electronics';

This script creates a `products` table. It inserts sample data for various products. The `SELECT` statement first filters for products in the ‘Electronics’ category. Then, it sorts the filtered results in ascending order based on `price` using `ORDER BY price ASC`. Finally, it limits the output to the first 3 products. The final `SELECT` statement validates the count of electronics products.

Output:

3