SQL Keywords

SQL Keywords

DISTINCT / LIMIT / OFFSET

Feb 23, 2026

·

5

min read

Category: Data Query Language (DQL)

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

DISTINCT removes duplicate rows from results, LIMIT restricts the number of rows returned, and OFFSET skips a specified number of rows. Essential for pagination, removing duplicates, and controlling result set size.

Syntax

DISTINCT:

SELECT DISTINCT column1, column2, ...
FROM


SELECT DISTINCT column1, column2, ...
FROM


SELECT DISTINCT column1, column2, ...
FROM

LIMIT:

-- Snowflake, Databricks
SELECT * FROM table_name LIMIT n;

-- BigQuery
SELECT * FROM table_name LIMIT


-- Snowflake, Databricks
SELECT * FROM table_name LIMIT n;

-- BigQuery
SELECT * FROM table_name LIMIT


-- Snowflake, Databricks
SELECT * FROM table_name LIMIT n;

-- BigQuery
SELECT * FROM table_name LIMIT

OFFSET:

SELECT * FROM table_name
LIMIT n OFFSET m;  -- Skip m rows, return n rows
SELECT * FROM table_name
LIMIT n OFFSET m;  -- Skip m rows, return n rows
SELECT * FROM table_name
LIMIT n OFFSET m;  -- Skip m rows, return n rows

Platform-Specific Notes

Snowflake:

  • Supports both LIMIT and TOP

  • OFFSET must be used with LIMIT

  • ORDER BY recommended with LIMIT for deterministic results

BigQuery:

  • LIMIT only (no TOP)

  • OFFSET requires LIMIT

  • Can use LIMIT in subqueries

Databricks:

  • Supports LIMIT and TOP

  • OFFSET requires LIMIT

  • Standard Spark SQL syntax

Example 1: DISTINCT - Remove Duplicates

All Platforms:

-- Get unique countries
SELECT DISTINCT country
FROM customers
ORDER BY country;

-- Get unique combinations
SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

-- Count distinct values
SELECT 
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT product_id) as unique_products
FROM


-- Get unique countries
SELECT DISTINCT country
FROM customers
ORDER BY country;

-- Get unique combinations
SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

-- Count distinct values
SELECT 
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT product_id) as unique_products
FROM


-- Get unique countries
SELECT DISTINCT country
FROM customers
ORDER BY country;

-- Get unique combinations
SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

-- Count distinct values
SELECT 
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT product_id) as unique_products
FROM

Sample Data (customers table):

customer_id

name

country

city

1

John

USA

New York

2

Maria

Spain

Madrid

3

Sarah

USA

New York

4

Bob

USA

Chicago

5

Carlos

Spain

Barcelona

Result (DISTINCT country):

country

Spain

USA

Result (DISTINCT country, city):

country

city

Spain

Barcelona

Spain

Madrid

USA

Chicago

USA

New York

Example 2: LIMIT - Return Top N Rows

All Platforms:

-- Top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Top 10 recent orders
SELECT order_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Sample data for testing
SELECT *
FROM large_table
LIMIT 100;  -- Preview first 100 rows
-- Top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Top 10 recent orders
SELECT order_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Sample data for testing
SELECT *
FROM large_table
LIMIT 100;  -- Preview first 100 rows
-- Top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Top 10 recent orders
SELECT order_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Sample data for testing
SELECT *
FROM large_table
LIMIT 100;  -- Preview first 100 rows

Sample Data (products table):

product_id

product_name

price

1

Laptop

999.99

2

Monitor

349.99

3

Keyboard

89.99

4

Mouse

29.99

5

Webcam

119.99

6

Headset

79.99

Result (LIMIT 3):

product_name

price

Laptop

999.99

Monitor

349.99

Webcam

119.99

Example 3: LIMIT with OFFSET - Pagination

All Platforms:

-- Page 1 (rows 1-10)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Generic pagination formula
-- LIMIT page_size OFFSET (page_number - 1) * page_size
-- Page 1 (rows 1-10)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Generic pagination formula
-- LIMIT page_size OFFSET (page_number - 1) * page_size
-- Page 1 (rows 1-10)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Generic pagination formula
-- LIMIT page_size OFFSET (page_number - 1) * page_size

Pagination Example:

-- Variables for pagination
SET page_size = 25;
SET page_number = 3;

SELECT *
FROM products
ORDER BY product_id
LIMIT $page_size OFFSET ($page_number - 1)


-- Variables for pagination
SET page_size = 25;
SET page_number = 3;

SELECT *
FROM products
ORDER BY product_id
LIMIT $page_size OFFSET ($page_number - 1)


-- Variables for pagination
SET page_size = 25;
SET page_number = 3;

SELECT *
FROM products
ORDER BY product_id
LIMIT $page_size OFFSET ($page_number - 1)

Example 4: DISTINCT with Aggregation

All Platforms:

SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(DISTINCT job_title) as unique_job_titles,
    COUNT(DISTINCT EXTRACT(YEAR FROM hire_date)) as hiring_years,
    AVG(DISTINCT salary) as avg_unique_salaries
FROM employees
GROUP BY


SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(DISTINCT job_title) as unique_job_titles,
    COUNT(DISTINCT EXTRACT(YEAR FROM hire_date)) as hiring_years,
    AVG(DISTINCT salary) as avg_unique_salaries
FROM employees
GROUP BY


SELECT 
    department,
    COUNT(*) as total_employees,
    COUNT(DISTINCT job_title) as unique_job_titles,
    COUNT(DISTINCT EXTRACT(YEAR FROM hire_date)) as hiring_years,
    AVG(DISTINCT salary) as avg_unique_salaries
FROM employees
GROUP BY

Sample Data (employees table):

employee_id

name

department

job_title

salary

hire_date

1

Alice

Sales

Manager

85000

2020-01-15

2

Bob

Sales

Rep

55000

2021-03-10

3

Carol

Sales

Rep

55000

2021-06-20

4

David

IT

Engineer

95000

2020-05-01

5

Emma

IT

Engineer

92000

2021-08-15

Result:

department

total_employees

unique_job_titles

hiring_years

avg_unique_salaries

IT

2

1

2

93500.00

Sales

3

2

2

70000.00

Example 5: DISTINCT ON (Snowflake/Databricks)

Snowflake:

-- Get first order for each customer (using QUALIFY instead)
SELECT 
    customer_id,
    order_id,
    order_date,
    total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) = 1


-- Get first order for each customer (using QUALIFY instead)
SELECT 
    customer_id,
    order_id,
    order_date,
    total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) = 1


-- Get first order for each customer (using QUALIFY instead)
SELECT 
    customer_id,
    order_id,
    order_date,
    total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) = 1

Alternative for all platforms:

WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as rn
    FROM orders
)
SELECT customer_id, order_id, order_date, total
FROM ranked_orders
WHERE rn = 1


WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as rn
    FROM orders
)
SELECT customer_id, order_id, order_date, total
FROM ranked_orders
WHERE rn = 1


WITH ranked_orders AS (
    SELECT 
        customer_id,
        order_id,
        order_date,
        total,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as rn
    FROM orders
)
SELECT customer_id, order_id, order_date, total
FROM ranked_orders
WHERE rn = 1

Example 6: Combine DISTINCT, LIMIT, OFFSET

All Platforms:

-- Get unique product categories, paginated
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 10 OFFSET 0;

-- Top 5 countries by customer count
SELECT 
    country,
    COUNT(DISTINCT customer_id) as customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC
LIMIT 5


-- Get unique product categories, paginated
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 10 OFFSET 0;

-- Top 5 countries by customer count
SELECT 
    country,
    COUNT(DISTINCT customer_id) as customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC
LIMIT 5


-- Get unique product categories, paginated
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 10 OFFSET 0;

-- Top 5 countries by customer count
SELECT 
    country,
    COUNT(DISTINCT customer_id) as customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC
LIMIT 5

DISTINCT vs GROUP BY

Aspect

DISTINCT

GROUP BY

Purpose

Remove duplicates

Aggregate data

Aggregation

No

Yes

Performance

Similar

Similar

Flexibility

Limited

High

Use case

Simple deduplication

Analysis with aggregates

Equivalent queries:

-- These produce the same result
SELECT DISTINCT country FROM customers;
SELECT country FROM customers GROUP BY


-- These produce the same result
SELECT DISTINCT country FROM customers;
SELECT country FROM customers GROUP BY


-- These produce the same result
SELECT DISTINCT country FROM customers;
SELECT country FROM customers GROUP BY

Pagination Best Practices

Method 1: LIMIT/OFFSET (Simple but slow for large offsets)

SELECT * FROM products
ORDER BY product_id
LIMIT 25 OFFSET 1000;  -- Slow: scans 1000 rows to skip them
SELECT * FROM products
ORDER BY product_id
LIMIT 25 OFFSET 1000;  -- Slow: scans 1000 rows to skip them
SELECT * FROM products
ORDER BY product_id
LIMIT 25 OFFSET 1000;  -- Slow: scans 1000 rows to skip them

Method 2: Keyset Pagination (Faster for large datasets)

-- Page 1
SELECT * FROM products
ORDER BY product_id
LIMIT 25;

-- Page 2 (where last_id from previous page)
SELECT * FROM products
WHERE product_id > last_id
ORDER BY product_id
LIMIT 25


-- Page 1
SELECT * FROM products
ORDER BY product_id
LIMIT 25;

-- Page 2 (where last_id from previous page)
SELECT * FROM products
WHERE product_id > last_id
ORDER BY product_id
LIMIT 25


-- Page 1
SELECT * FROM products
ORDER BY product_id
LIMIT 25;

-- Page 2 (where last_id from previous page)
SELECT * FROM products
WHERE product_id > last_id
ORDER BY product_id
LIMIT 25

Performance Considerations

DISTINCT:

  • Requires sorting or hashing (expensive)

  • Index on DISTINCT columns helps

  • Consider GROUP BY for complex queries

LIMIT:

  • Very efficient (stops after n rows)

  • Must include ORDER BY for deterministic results

  • Use for sampling and testing

OFFSET:

  • Scans and discards offset rows (slow for large offsets)

  • O(n) performance where n = offset

  • Consider keyset pagination for large offsets

Common Use Cases

DISTINCT:

  1. Unique values: Get all unique categories, tags

  2. Deduplication: Remove duplicate records

  3. Data quality: Find unique combinations

  4. Reporting: Count distinct customers, products

LIMIT:

  1. Top N queries: Best sellers, highest scores

  2. Sampling: Test queries on subset

  3. Performance: Limit result size

  4. Dashboards: Show recent items

OFFSET:

  1. Pagination: Web applications, reports

  2. Batch processing: Process data in chunks

  3. APIs: Return pages of results

Best Practices

  1. Always use ORDER BY with LIMIT: Ensure deterministic results

  2. Index columns in ORDER BY: Improves LIMIT performance

  3. Avoid large OFFSET: Use keyset pagination instead

  4. Test DISTINCT performance: May be expensive on large tables

  5. Use LIMIT for testing: Preview results before full query

  6. Consider COUNT(DISTINCT): More efficient than DISTINCT + COUNT

  7. Document pagination logic: Make page calculations clear

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

More Articles

Learn

·

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

·

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

·

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

·

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

·

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

·

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

paradime tableau integration

Product

·

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

paradime tableau integration

Product

·

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

paradime tableau integration

Product

·

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

decorative icon

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

decorative icon

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

decorative icon

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Start for free

Platform

Code IDE

Bolt and CI/CD

Snowflake Cost

BigQuery Cost

dbt™ Monitoring

Data Alerts

Integrations

Data Mesh

Why Paradime

Resources

Product Docs

Case Studies

Blog

Videos

SQL Resources

Compare dbt Cloud™

Media Kit

ADD-ONs

Enterprise Pack

Security Pack

Follow us

Paradime AWS marketplace link
Paradime AWS qualified software badge

Industries

Financial Services

Healthcare

Media

About

Company

Career

Pricing

Legal

Terms of Service

Privacy Policy

Trust Center

Copyright © 2026 Paradime Labs, Inc.

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Code IDE

Bolt and CI/CD

Snowflake Cost

BigQuery Cost

dbt™ Monitoring

Data Alerts

Integrations

Data Mesh

Why Paradime

Resources

Product Docs

Case Studies

Blog

Videos

SQL Resources

Compare dbt Cloud™

Media Kit

ADD-ONs

Enterprise Pack

Security Pack

Follow us

Industries

Financial Services

Healthcare

Media

About

Company

Career

Pricing

Legal

Terms of Service

Privacy Policy

Trust Center

Copyright © 2026 Paradime Labs, Inc.

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Code IDE

Bolt and CI/CD

Snowflake Cost

BigQuery Cost

dbt™ Monitoring

Data Alerts

Integrations

Data Mesh

Why Paradime

Resources

Product Docs

Case Studies

Blog

Videos

SQL Resources

Compare dbt Cloud™

Media Kit

ADD-ONs

Enterprise Pack

Security Pack

Follow us

Industries

Financial Services

Healthcare

Media

About

Company

Career

Pricing

Legal

Terms of Service

Privacy Policy

Trust Center

Copyright © 2026 Paradime Labs, Inc.

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.