Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

3 / 5

LIMIT, TOP, and FETCH FIRST

Constraining Result Set Size

Databases often contain millions or billions of rows. Queries without row limits can return overwhelming result sets that consume excessive memory, network bandwidth, and processing time. More fundamentally, users rarely need all matching rows—they want the top 10 products, the latest 50 orders, or a single best match.

SQL provides mechanisms to constrain the number of rows returned, but the syntax varies significantly across database systems. This fragmentation reflects SQL's evolution: different vendors implemented row limiting before it was standardized, and legacy syntax persists alongside the modern SQL standard approach.

This page provides comprehensive coverage of all major row limiting syntaxes, enabling you to write efficient queries across any database system.

What You Will Learn

By the end of this page, you will understand: (1) The LIMIT clause (MySQL, PostgreSQL, SQLite), (2) The TOP clause (SQL Server, MS Access), (3) The FETCH FIRST clause (SQL Standard, DB2, Oracle 12c+), (4) ROWNUM pseudo-column (Oracle legacy), (5) Semantic differences between approaches, (6) Cross-database compatibility strategies, and (7) Performance considerations for row limiting.

The LIMIT Clause

The LIMIT clause is the most widely used row limiting syntax, supported by MySQL, PostgreSQL, SQLite, MariaDB, and many other database systems. It appears at the end of the query and specifies the maximum number of rows to return.

limit_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Basic LIMIT syntax
SELECT column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column
LIMIT number;
 
-- Example: Get the 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Example: Get 5 most recent orders
SELECT order_id, customer_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 5;
 
-- LIMIT without ORDER BY (non-deterministic)
SELECT * FROM products LIMIT 5;
-- Returns 5 rows, but which 5 is undefined!
-- The database may return different rows on repeated execution

Critical: ORDER BY Requirement

LIMIT without ORDER BY is almost always a mistake. The database makes no guarantee about which rows are returned—it might pick rows from anywhere in the table, and results may vary between executions. Always use ORDER BY with LIMIT to get deterministic, meaningful results.

1.1 LIMIT Clause Position

LIMIT must appear after all other clauses except the database-specific locking clauses. The full clause order is:

limit_position.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Complete clause order with LIMIT
SELECT columns
FROM table_name
WHERE conditions
GROUP BY columns
HAVING group_conditions
ORDER BY columns
LIMIT number;
 
-- Example with all clauses
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC
LIMIT 5;
-- Top 5 departments by average salary among recent hires

1.2 LIMIT with Expressions and Variables

Different databases have varying support for dynamic LIMIT values:

limit_dynamic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- PostgreSQL: LIMIT can be an expression
SELECT * FROM products ORDER BY price LIMIT 5 + 5;  -- Returns 10 rows
 
-- MySQL: LIMIT accepts integers or user variables
SET @n = 10;
SELECT * FROM products ORDER BY price LIMIT @n;  -- Syntax error in some versions!
-- In MySQL, LIMIT typically requires a literal integer
-- For dynamic limits, use prepared statements:
PREPARE stmt FROM 'SELECT * FROM products ORDER BY price LIMIT ?';
SET @n = 10;
EXECUTE stmt USING @n;
 
-- PostgreSQL: LIMIT can use a subquery (with restrictions)
SELECT * FROM products 
ORDER BY price 
LIMIT (SELECT setting::int FROM config WHERE name = 'page_size');
 
-- LIMIT ALL: Returns all rows (PostgreSQL)
SELECT * FROM products LIMIT ALL;  -- Same as no LIMIT
SELECT * FROM products LIMIT NULL;  -- Also returns all rows

1.3 LIMIT Edge Cases

limit_edge_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- LIMIT 0: Returns no rows (useful for schema queries)
SELECT * FROM products LIMIT 0;
-- Returns 0 rows but shows column structure
-- Some BI tools use this to discover schema
 
-- LIMIT larger than result set: Returns all matching rows
SELECT * FROM products WHERE category = 'Electronics' LIMIT 10000;
-- If only 50 electronics products exist, returns 50 rows
-- No error or padding to 10000 rows
 
-- Negative LIMIT: Syntax error in most databases
SELECT * FROM products LIMIT -1;  -- Error!
 
-- LIMIT with no matching rows: Empty result set
SELECT * FROM products WHERE category = 'NonExistent' LIMIT 10;
-- Returns 0 rows

The TOP Clause (SQL Server)

Microsoft SQL Server (and MS Access) uses the TOP clause for row limiting. Unlike LIMIT, TOP appears immediately after SELECT and before the column list.

top_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Basic TOP syntax (SQL Server)
SELECT TOP (number) column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column;
 
-- Example: Get 10 most expensive products
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;
 
-- Parentheses are optional but recommended
SELECT TOP 10 product_name, price
FROM products
ORDER BY price DESC;
 
-- Example: Get the single most recent order
SELECT TOP (1) *
FROM orders
ORDER BY order_date DESC;

2.1 TOP with PERCENT

SQL Server's TOP supports percentage-based limiting—unique functionality not available in LIMIT:

top_percent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- TOP with PERCENT: Return a percentage of rows
SELECT TOP (10) PERCENT product_name, price
FROM products
ORDER BY price DESC;
-- Returns the top 10% most expensive products
 
-- If the table has 1000 products, returns 100 rows
-- If the table has 85 products, returns 9 rows (ceiling of 8.5)
 
-- PERCENT with decimal values (SQL Server 2008+)
SELECT TOP (5.5) PERCENT *
FROM large_table;
-- Returns ceiling of 5.5% of rows
 
-- Practical use case: Sampling
SELECT TOP (1) PERCENT *
FROM transactions
ORDER BY NEWID();  -- Random order
-- Random 1% sample of transactions

2.2 TOP with WITH TIES

The WITH TIES option includes additional rows that match the last row's ORDER BY values, ensuring you don't arbitrarily exclude rows with identical rankings:

top_with_ties.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Sample data:
-- product_name  | price
-- Product A     | 100
-- Product B     | 95
-- Product C     | 90
-- Product D     | 90   <-- tie with Product C
-- Product E     | 85
 
-- TOP without WITH TIES: Exactly 3 rows
SELECT TOP (3) product_name, price
FROM products
ORDER BY price DESC;
-- Returns: Product A (100), Product B (95), Product C (90)
-- Product D (also 90) is excluded arbitrarily
 
-- TOP with WITH TIES: 3 rows plus ties
SELECT TOP (3) WITH TIES product_name, price
FROM products
ORDER BY price DESC;
-- Returns: Product A (100), Product B (95), Product C (90), Product D (90)
-- Returns 4 rows because Product D ties with the 3rd position
 
-- WITH TIES requires ORDER BY
SELECT TOP (3) WITH TIES product_name, price
FROM products;
-- Error: WITH TIES requires ORDER BY clause
 
-- Use case: Top 10 scores (including ties)
SELECT TOP (10) WITH TIES 
    student_name, 
    test_score
FROM exam_results
ORDER BY test_score DESC;
-- Returns at least 10 students, possibly more if scores tie at position 10

When to Use WITH TIES

Use WITH TIES when arbitrary cutoffs would be unfair or misleading—such as leaderboards, top performers, or threshold-based selections. If you want exactly N rows, omit WITH TIES. If you want 'all rows in the top N positions,' include it.

2.3 TOP with Expressions

SQL Server allows expressions in TOP, enabling dynamic row limits:

top_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- TOP with variable
DECLARE @n INT = 10;
SELECT TOP (@n) * FROM products ORDER BY price DESC;
 
-- TOP with expression
SELECT TOP (5 * 2) * FROM products ORDER BY price DESC;
 
-- TOP with subquery (requires parentheses)
SELECT TOP ((SELECT value FROM settings WHERE name = 'page_size'))
    *
FROM products
ORDER BY price DESC;
 
-- TOP in stored procedure parameter
CREATE PROCEDURE GetTopProducts 
    @TopN INT
AS
BEGIN
    SELECT TOP (@TopN) product_name, price
    FROM products
    ORDER BY price DESC;
END;
 
EXEC GetTopProducts @TopN = 15;

FETCH FIRST: The SQL Standard

The SQL:2008 standard introduced FETCH FIRST as the official row limiting syntax. It's now supported by PostgreSQL, Oracle (12c+), DB2, SQL Server (2012+), and other standards-compliant databases. This is the recommended approach for new development when portability matters.

fetch_first_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard FETCH FIRST syntax
SELECT column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column
FETCH FIRST n ROWS ONLY;
 
-- Alternative: FETCH NEXT (equivalent)
FETCH NEXT n ROWS ONLY;
 
-- ROW (singular) also accepted
FETCH FIRST 1 ROW ONLY;       -- Preferred for single row
FETCH FIRST 10 ROWS ONLY;     -- Preferred for multiple rows
 
-- Example: Get 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;
 
-- Example: Get single most recent order
SELECT *
FROM orders
ORDER BY order_date DESC
FETCH FIRST 1 ROW ONLY;

3.1 FETCH with OFFSET (Complete Syntax)

FETCH FIRST is part of a more complete syntax that includes OFFSET for pagination:

fetch_offset_complete.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Complete standard syntax
SELECT columns
FROM table
ORDER BY column
OFFSET n ROWS
FETCH FIRST m ROWS ONLY;
 
-- Note: OFFSET must come before FETCH
-- OFFSET is required if using FETCH (in strict SQL standard)
 
-- Example: Second page of 10 results
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS        -- Skip first 10
FETCH FIRST 10 ROWS ONLY;  -- Return next 10
 
-- First page (OFFSET 0)
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
 
-- Singular/plural variations (all equivalent)
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
OFFSET 0 ROW FETCH FIRST 10 ROWS ONLY;
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
OFFSET 0 ROW FETCH NEXT 10 ROW ONLY;

3.2 FETCH with WITH TIES

Like SQL Server's TOP, the standard FETCH also supports WITH TIES:

fetch_with_ties.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- FETCH with WITH TIES
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;
 
-- Returns at least 3 rows, plus any that tie with position 3
 
-- PERCENT option (where supported)
SELECT *
FROM products
ORDER BY price DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
 
-- Combining OFFSET with WITH TIES
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS WITH TIES;
-- Skip 10, then return at least 5 (plus ties at position 15)

Standard Compliance Variations

While FETCH FIRST is the SQL standard, implementations vary. PostgreSQL is very permissive (OFFSET optional). Oracle requires ORDER BY with FETCH. SQL Server added FETCH in 2012 but still supports TOP. Always check your specific database version's documentation.

ROWNUM: Oracle Legacy Approach

Before Oracle 12c (2013), Oracle Database lacked LIMIT or FETCH syntax. Instead, Oracle used the ROWNUM pseudo-column—a row counter assigned during query processing. Understanding ROWNUM is essential for maintaining legacy Oracle code.

rownum_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ROWNUM pseudo-column
SELECT product_name, price, ROWNUM
FROM products
WHERE ROWNUM <= 10;
 
-- Returns first 10 rows encountered (NOT ordered!)
 
-- ROWNUM is assigned BEFORE ORDER BY
SELECT product_name, price
FROM products
WHERE ROWNUM <= 5
ORDER BY price DESC;
-- WRONG: Gets 5 arbitrary rows, then sorts those 5
-- Does NOT get the 5 most expensive products!

ROWNUM Ordering Trap

The most dangerous ROWNUM mistake: ROWNUM is assigned during the WHERE phase, BEFORE ORDER BY. If you want 'top N by some order,' you must use a subquery to order first, then filter by ROWNUM in the outer query.

rownum_correct.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- CORRECT: Top 10 by price using subquery
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) WHERE ROWNUM <= 10;
 
-- Execution flow:
-- 1. Inner query selects and orders all products
-- 2. Outer query assigns ROWNUM to ordered results
-- 3. WHERE ROWNUM <= 10 keeps first 10 of ordered results
 
-- Alternative: Using inline view with alias
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) top_products
WHERE ROWNUM <= 10;

4.1 ROWNUM Comparison Restrictions

ROWNUM has counterintuitive comparison behavior:

rownum_restrictions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- WORKS: ROWNUM with <= or <
SELECT * FROM products WHERE ROWNUM <= 10;  -- First 10 rows
SELECT * FROM products WHERE ROWNUM < 11;   -- Same as above
 
-- WORKS: ROWNUM = 1 (only)
SELECT * FROM products WHERE ROWNUM = 1;    -- First 1 row
 
-- DOES NOT WORK: ROWNUM > n or ROWNUM = n (where n > 1)
SELECT * FROM products WHERE ROWNUM > 5;    -- Returns NO rows!
SELECT * FROM products WHERE ROWNUM = 5;    -- Returns NO rows!
 
-- Why? ROWNUM assignment algorithm:
-- 1. Get a candidate row
-- 2. If it passes WHERE, assign next ROWNUM (starting at 1)
-- 3. For ROWNUM > 5: first row gets ROWNUM 1, fails condition, so
--    ROWNUM stays at 1. Next row would get 1, also fails. Loop forever.
-- 4. No row ever reaches ROWNUM = 5 because no row gets past 1
 
-- Solution for ROWNUM > n: Use subquery
SELECT * FROM (
    SELECT product_name, price, ROWNUM as rn
    FROM products
    ORDER BY price DESC
) WHERE rn > 5 AND rn <= 15;
-- Rows 6-15 (for pagination)

4.2 Modern Oracle: Prefer FETCH FIRST

Oracle 12c introduced FETCH FIRST, eliminating the need for ROWNUM workarounds:

oracle_modern.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Legacy Oracle (pre-12c): Nested subquery required
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) WHERE ROWNUM <= 10;
 
-- Modern Oracle (12c+): Clean FETCH FIRST syntax
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;
 
-- Legacy pagination: Double-nested nightmare
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT product_name, price
        FROM products
        ORDER BY price DESC
    ) a WHERE ROWNUM <= 20  -- Upper bound (offset + limit)
) WHERE rn > 10;  -- Lower bound (offset)
 
-- Modern pagination: Clean and readable
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

Cross-Database Syntax Comparison

When writing portable SQL or migrating between databases, understanding syntax equivalences is crucial. Here's a comprehensive comparison:

Row Limiting Syntax by Database
Database	Primary Syntax	Also Supports	Notes
MySQL	LIMIT n		LIMIT must be literal in older versions
PostgreSQL	LIMIT n	FETCH FIRST	LIMIT predates standard adoption
SQLite	LIMIT n		LIMIT only, no FETCH support
SQL Server 2012+	TOP (n)	OFFSET FETCH	TOP for backward compat
SQL Server (older)	TOP (n)		No FETCH FIRST support
Oracle 12c+	FETCH FIRST	ROWNUM	ROWNUM for legacy code
Oracle (older)	ROWNUM		Requires subquery tricks
DB2	FETCH FIRST		Standard compliant
MariaDB	LIMIT n		MySQL-compatible

5.1 Equivalent Queries Across Databases

The same logical query ('top 10 products by price') in each syntax:

top10_mysql.sql

-- MySQL: LIMIT clause
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

5.2 Portability Strategies

When writing SQL that needs to work across multiple databases:

Cross-Database Portability Approaches

•Use FETCH FIRST for new projects — It's the SQL standard and supported by most modern database versions. Future-proof choice.
•Abstract at the application layer — Use an ORM or query builder that generates appropriate syntax for each database.
•Create database-specific views — Encapsulate limiting logic in views that differ per database, with consistent view names.
•Document database assumptions — If using LIMIT for simplicity, document the MySQL/PostgreSQL dependency clearly.
•Test on all target databases — Syntax acceptance doesn't guarantee identical behavior. Test edge cases on each platform.

Performance Considerations

Row limiting can dramatically improve query performance—but only if the database can leverage it during query execution. Understanding when and how limiting optimizes performance helps you write efficient queries.

6.1 Top-N Query Optimization

When you want the first N rows by some ordering, and an index exists on the ORDER BY column, the database can use a top-N sort optimization:

topn_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Index on products(price)
CREATE INDEX idx_price ON products(price DESC);
 
-- Highly optimized query
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Execution:
-- 1. Uses index to read products in price DESC order
-- 2. Stops after 10 rows (no full table scan!)
-- 3. Execution time: constant regardless of table size
 
-- Compare without index or with non-indexed ORDER BY:
SELECT product_name, price
FROM products
ORDER BY RANDOM()  -- Can't use index
LIMIT 10;
 
-- Execution:
-- 1. Full table scan (reads all rows)
-- 2. Sorts ALL rows randomly
-- 3. Returns first 10
-- 4. Execution time: proportional to table size

6.2 When LIMIT Cannot Optimize

LIMIT cannot reduce work in several scenarios:

LIMIT Optimization Barriers

•Aggregation before LIMIT — SELECT SUM(x) LIMIT 10 still computes the full sum before limiting (sum returns 1 row anyway).
•DISTINCT before LIMIT — SELECT DISTINCT x LIMIT 10 may need to scan many rows to find 10 unique values.
•Non-indexed ORDER BY — Without an index, ALL rows must be sorted to find the top N.
•Complex WHERE filters — Even with LIMIT 1, if no index supports the WHERE clause, a full scan may occur.
•Joins that multiply rows — If a join produces 10 million rows before LIMIT 10 is applied, significant work occurs before limiting.

limit_barriers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- LIMIT cannot optimize these queries:
 
-- Aggregation: Must process all rows for sum
SELECT department, SUM(salary) as total
FROM employees
GROUP BY department
ORDER BY total DESC
LIMIT 5;
-- Must compute SUM for ALL departments first
 
-- DISTINCT with low cardinality: May scan many rows
SELECT DISTINCT status
FROM orders
LIMIT 3;
-- If there are only 4 statuses spread across 10M orders,
-- might scan millions to find 3 unique values
 
-- Non-indexed ORDER BY
SELECT * FROM large_table ORDER BY non_indexed_column LIMIT 10;
-- Full table scan + full sort, then return 10
 
-- Optimize with covering index:
CREATE INDEX idx_covering ON large_table(non_indexed_column) 
INCLUDE (other_columns);

6.3 EXPLAIN Analysis

Always use EXPLAIN to verify that LIMIT is being used effectively:

limit_explain.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- PostgreSQL EXPLAIN analysis
EXPLAIN (ANALYZE, BUFFERS) 
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Good plan indicator:
-- Limit  (cost=0.42..0.68 rows=10)
--   ->  Index Scan Backward using idx_price on products (rows=10)
-- Shows: Index scan, low row count, stopped early
 
-- Bad plan indicator:
-- Limit  (cost=15000..15001 rows=10)
--   ->  Sort  (cost=15000..16000 rows=100000)
--     ->  Seq Scan on products (rows=100000)
-- Shows: Full scan, full sort, then limit (expensive)
 
-- SQL Server equivalent
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;
-- Check for 'logical reads' - lower is better

Index Design for Top-N

For frequently-run top-N queries, create indexes that match the ORDER BY clause. For ORDER BY price DESC LIMIT 10, an index on (price DESC) enables optimal performance. The database reads exactly 10 index entries and stops.

Common LIMIT/TOP Use Cases

Row limiting is fundamental to many application patterns. Here are the most common use cases:

limit_use_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Use Case 1: Existence Check (is there at least one?)
SELECT 1 FROM orders WHERE customer_id = 123 LIMIT 1;
-- Fast existence check - stops at first match
-- Returns 1 row if exists, 0 if not
 
-- Use Case 2: Get Single Record by ID
SELECT * FROM users WHERE user_id = 456 LIMIT 1;
-- Even with unique ID, LIMIT 1 can hint the optimizer
 
-- Use Case 3: Most Recent Record
SELECT * FROM audit_log 
WHERE entity_id = 789 
ORDER BY created_at DESC 
LIMIT 1;
-- Get the latest audit entry for an entity
 
-- Use Case 4: Top N List
SELECT player_name, score
FROM game_scores
WHERE game_date = CURRENT_DATE
ORDER BY score DESC
LIMIT 10;
-- Daily leaderboard top 10
 
-- Use Case 5: Random Sample
SELECT * FROM products
ORDER BY RANDOM()  -- PostgreSQL/SQLite
LIMIT 5;
-- Random 5 products (expensive for large tables!)
 
-- SQL Server random sample:
SELECT TOP (5) * FROM products ORDER BY NEWID();
 
-- Use Case 6: Data Preview
SELECT * FROM large_import_table LIMIT 100;
-- Quick preview of imported data structure/content
 
-- Use Case 7: Batch Processing Loop
-- Fetch batches of 1000 for processing
SELECT * FROM pending_jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at ASC
LIMIT 1000;
-- Process 1000, mark complete, repeat
 
-- Use Case 8: Pagination (covered in detail next page)
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;  -- Page 3 of 20 items per page

Existence Checks: EXISTS vs. LIMIT 1

For existence checks, both SELECT 1 WHERE EXISTS (subquery) and subquery LIMIT 1 work. EXISTS is generally preferred as it's semantically clearer and can never accidentally return multiple rows.

Summary: Row Limiting Across Databases

Row limiting is an essential SQL skill, complicated by the variety of syntaxes across database systems. Let's consolidate the key takeaways:

Key Takeaways

•Three main syntaxes exist — LIMIT (MySQL, PostgreSQL, SQLite), TOP (SQL Server), and FETCH FIRST (SQL standard). Know which your database uses.
•FETCH FIRST is the SQL standard — For new, portable code, prefer the SQL:2008 syntax: FETCH FIRST n ROWS ONLY. It's widely supported in modern database versions.
•Always use ORDER BY with limiting — Without ORDER BY, which rows are returned is undefined and non-deterministic. Results may vary between executions.
•WITH TIES handles equal rankings — Use it when arbitrary cutoffs are unacceptable (leaderboards, thresholds). Omit it when you need exactly N rows.
•TOP supports PERCENT — SQL Server's TOP (n) PERCENT returns a fraction of rows—useful for sampling.
•ROWNUM is Oracle legacy — It works but requires subqueries for ordered limiting. Migrate to FETCH FIRST on Oracle 12c+.
•Performance depends on indexes — With proper indexes, top-N queries are extremely fast (read only N rows). Without indexes, full scans may occur despite LIMIT.
•LIMIT is applied last — After WHERE, GROUP BY, ORDER BY. It limits the final result set, not intermediate processing.

What's Next:

With LIMIT/TOP/FETCH mastered, we'll explore OFFSET—the companion clause that enables skipping rows. The next page covers OFFSET syntax, performance implications, and why it's both essential and problematic for pagination.

Page Complete

You now have comprehensive knowledge of row limiting across all major database systems—LIMIT, TOP, FETCH FIRST, and ROWNUM. You can write efficient, portable queries that return controlled result sets regardless of which database you're using.

3 / 5

Loading learning content...

Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

3 / 5

LIMIT, TOP, and FETCH FIRST

Constraining Result Set Size

This page provides comprehensive coverage of all major row limiting syntaxes, enabling you to write efficient queries across any database system.

What You Will Learn

The LIMIT Clause

limit_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Basic LIMIT syntax
SELECT column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column
LIMIT number;
 
-- Example: Get the 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Example: Get 5 most recent orders
SELECT order_id, customer_id, order_date, total
FROM orders
ORDER BY order_date DESC
LIMIT 5;
 
-- LIMIT without ORDER BY (non-deterministic)
SELECT * FROM products LIMIT 5;
-- Returns 5 rows, but which 5 is undefined!
-- The database may return different rows on repeated execution

Critical: ORDER BY Requirement

1.1 LIMIT Clause Position

LIMIT must appear after all other clauses except the database-specific locking clauses. The full clause order is:

limit_position.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Complete clause order with LIMIT
SELECT columns
FROM table_name
WHERE conditions
GROUP BY columns
HAVING group_conditions
ORDER BY columns
LIMIT number;
 
-- Example with all clauses
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC
LIMIT 5;
-- Top 5 departments by average salary among recent hires

1.2 LIMIT with Expressions and Variables

Different databases have varying support for dynamic LIMIT values:

limit_dynamic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- PostgreSQL: LIMIT can be an expression
SELECT * FROM products ORDER BY price LIMIT 5 + 5;  -- Returns 10 rows
 
-- MySQL: LIMIT accepts integers or user variables
SET @n = 10;
SELECT * FROM products ORDER BY price LIMIT @n;  -- Syntax error in some versions!
-- In MySQL, LIMIT typically requires a literal integer
-- For dynamic limits, use prepared statements:
PREPARE stmt FROM 'SELECT * FROM products ORDER BY price LIMIT ?';
SET @n = 10;
EXECUTE stmt USING @n;
 
-- PostgreSQL: LIMIT can use a subquery (with restrictions)
SELECT * FROM products 
ORDER BY price 
LIMIT (SELECT setting::int FROM config WHERE name = 'page_size');
 
-- LIMIT ALL: Returns all rows (PostgreSQL)
SELECT * FROM products LIMIT ALL;  -- Same as no LIMIT
SELECT * FROM products LIMIT NULL;  -- Also returns all rows

1.3 LIMIT Edge Cases

limit_edge_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- LIMIT 0: Returns no rows (useful for schema queries)
SELECT * FROM products LIMIT 0;
-- Returns 0 rows but shows column structure
-- Some BI tools use this to discover schema
 
-- LIMIT larger than result set: Returns all matching rows
SELECT * FROM products WHERE category = 'Electronics' LIMIT 10000;
-- If only 50 electronics products exist, returns 50 rows
-- No error or padding to 10000 rows
 
-- Negative LIMIT: Syntax error in most databases
SELECT * FROM products LIMIT -1;  -- Error!
 
-- LIMIT with no matching rows: Empty result set
SELECT * FROM products WHERE category = 'NonExistent' LIMIT 10;
-- Returns 0 rows

The TOP Clause (SQL Server)

Microsoft SQL Server (and MS Access) uses the TOP clause for row limiting. Unlike LIMIT, TOP appears immediately after SELECT and before the column list.

top_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Basic TOP syntax (SQL Server)
SELECT TOP (number) column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column;
 
-- Example: Get 10 most expensive products
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;
 
-- Parentheses are optional but recommended
SELECT TOP 10 product_name, price
FROM products
ORDER BY price DESC;
 
-- Example: Get the single most recent order
SELECT TOP (1) *
FROM orders
ORDER BY order_date DESC;

2.1 TOP with PERCENT

SQL Server's TOP supports percentage-based limiting—unique functionality not available in LIMIT:

top_percent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- TOP with PERCENT: Return a percentage of rows
SELECT TOP (10) PERCENT product_name, price
FROM products
ORDER BY price DESC;
-- Returns the top 10% most expensive products
 
-- If the table has 1000 products, returns 100 rows
-- If the table has 85 products, returns 9 rows (ceiling of 8.5)
 
-- PERCENT with decimal values (SQL Server 2008+)
SELECT TOP (5.5) PERCENT *
FROM large_table;
-- Returns ceiling of 5.5% of rows
 
-- Practical use case: Sampling
SELECT TOP (1) PERCENT *
FROM transactions
ORDER BY NEWID();  -- Random order
-- Random 1% sample of transactions

2.2 TOP with WITH TIES

The WITH TIES option includes additional rows that match the last row's ORDER BY values, ensuring you don't arbitrarily exclude rows with identical rankings:

top_with_ties.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Sample data:
-- product_name  | price
-- Product A     | 100
-- Product B     | 95
-- Product C     | 90
-- Product D     | 90   <-- tie with Product C
-- Product E     | 85
 
-- TOP without WITH TIES: Exactly 3 rows
SELECT TOP (3) product_name, price
FROM products
ORDER BY price DESC;
-- Returns: Product A (100), Product B (95), Product C (90)
-- Product D (also 90) is excluded arbitrarily
 
-- TOP with WITH TIES: 3 rows plus ties
SELECT TOP (3) WITH TIES product_name, price
FROM products
ORDER BY price DESC;
-- Returns: Product A (100), Product B (95), Product C (90), Product D (90)
-- Returns 4 rows because Product D ties with the 3rd position
 
-- WITH TIES requires ORDER BY
SELECT TOP (3) WITH TIES product_name, price
FROM products;
-- Error: WITH TIES requires ORDER BY clause
 
-- Use case: Top 10 scores (including ties)
SELECT TOP (10) WITH TIES 
    student_name, 
    test_score
FROM exam_results
ORDER BY test_score DESC;
-- Returns at least 10 students, possibly more if scores tie at position 10

When to Use WITH TIES

2.3 TOP with Expressions

SQL Server allows expressions in TOP, enabling dynamic row limits:

top_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- TOP with variable
DECLARE @n INT = 10;
SELECT TOP (@n) * FROM products ORDER BY price DESC;
 
-- TOP with expression
SELECT TOP (5 * 2) * FROM products ORDER BY price DESC;
 
-- TOP with subquery (requires parentheses)
SELECT TOP ((SELECT value FROM settings WHERE name = 'page_size'))
    *
FROM products
ORDER BY price DESC;
 
-- TOP in stored procedure parameter
CREATE PROCEDURE GetTopProducts 
    @TopN INT
AS
BEGIN
    SELECT TOP (@TopN) product_name, price
    FROM products
    ORDER BY price DESC;
END;
 
EXEC GetTopProducts @TopN = 15;

FETCH FIRST: The SQL Standard

fetch_first_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard FETCH FIRST syntax
SELECT column1, column2, ...
FROM table_name
WHERE conditions
ORDER BY column
FETCH FIRST n ROWS ONLY;
 
-- Alternative: FETCH NEXT (equivalent)
FETCH NEXT n ROWS ONLY;
 
-- ROW (singular) also accepted
FETCH FIRST 1 ROW ONLY;       -- Preferred for single row
FETCH FIRST 10 ROWS ONLY;     -- Preferred for multiple rows
 
-- Example: Get 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;
 
-- Example: Get single most recent order
SELECT *
FROM orders
ORDER BY order_date DESC
FETCH FIRST 1 ROW ONLY;

3.1 FETCH with OFFSET (Complete Syntax)

FETCH FIRST is part of a more complete syntax that includes OFFSET for pagination:

fetch_offset_complete.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Complete standard syntax
SELECT columns
FROM table
ORDER BY column
OFFSET n ROWS
FETCH FIRST m ROWS ONLY;
 
-- Note: OFFSET must come before FETCH
-- OFFSET is required if using FETCH (in strict SQL standard)
 
-- Example: Second page of 10 results
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS        -- Skip first 10
FETCH FIRST 10 ROWS ONLY;  -- Return next 10
 
-- First page (OFFSET 0)
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
 
-- Singular/plural variations (all equivalent)
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;
OFFSET 0 ROW FETCH FIRST 10 ROWS ONLY;
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
OFFSET 0 ROW FETCH NEXT 10 ROW ONLY;

3.2 FETCH with WITH TIES

Like SQL Server's TOP, the standard FETCH also supports WITH TIES:

fetch_with_ties.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- FETCH with WITH TIES
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;
 
-- Returns at least 3 rows, plus any that tie with position 3
 
-- PERCENT option (where supported)
SELECT *
FROM products
ORDER BY price DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
 
-- Combining OFFSET with WITH TIES
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS WITH TIES;
-- Skip 10, then return at least 5 (plus ties at position 15)

Standard Compliance Variations

ROWNUM: Oracle Legacy Approach

rownum_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ROWNUM pseudo-column
SELECT product_name, price, ROWNUM
FROM products
WHERE ROWNUM <= 10;
 
-- Returns first 10 rows encountered (NOT ordered!)
 
-- ROWNUM is assigned BEFORE ORDER BY
SELECT product_name, price
FROM products
WHERE ROWNUM <= 5
ORDER BY price DESC;
-- WRONG: Gets 5 arbitrary rows, then sorts those 5
-- Does NOT get the 5 most expensive products!

ROWNUM Ordering Trap

rownum_correct.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- CORRECT: Top 10 by price using subquery
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) WHERE ROWNUM <= 10;
 
-- Execution flow:
-- 1. Inner query selects and orders all products
-- 2. Outer query assigns ROWNUM to ordered results
-- 3. WHERE ROWNUM <= 10 keeps first 10 of ordered results
 
-- Alternative: Using inline view with alias
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) top_products
WHERE ROWNUM <= 10;

4.1 ROWNUM Comparison Restrictions

ROWNUM has counterintuitive comparison behavior:

rownum_restrictions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- WORKS: ROWNUM with <= or <
SELECT * FROM products WHERE ROWNUM <= 10;  -- First 10 rows
SELECT * FROM products WHERE ROWNUM < 11;   -- Same as above
 
-- WORKS: ROWNUM = 1 (only)
SELECT * FROM products WHERE ROWNUM = 1;    -- First 1 row
 
-- DOES NOT WORK: ROWNUM > n or ROWNUM = n (where n > 1)
SELECT * FROM products WHERE ROWNUM > 5;    -- Returns NO rows!
SELECT * FROM products WHERE ROWNUM = 5;    -- Returns NO rows!
 
-- Why? ROWNUM assignment algorithm:
-- 1. Get a candidate row
-- 2. If it passes WHERE, assign next ROWNUM (starting at 1)
-- 3. For ROWNUM > 5: first row gets ROWNUM 1, fails condition, so
--    ROWNUM stays at 1. Next row would get 1, also fails. Loop forever.
-- 4. No row ever reaches ROWNUM = 5 because no row gets past 1
 
-- Solution for ROWNUM > n: Use subquery
SELECT * FROM (
    SELECT product_name, price, ROWNUM as rn
    FROM products
    ORDER BY price DESC
) WHERE rn > 5 AND rn <= 15;
-- Rows 6-15 (for pagination)

4.2 Modern Oracle: Prefer FETCH FIRST

Oracle 12c introduced FETCH FIRST, eliminating the need for ROWNUM workarounds:

oracle_modern.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Legacy Oracle (pre-12c): Nested subquery required
SELECT * FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
) WHERE ROWNUM <= 10;
 
-- Modern Oracle (12c+): Clean FETCH FIRST syntax
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;
 
-- Legacy pagination: Double-nested nightmare
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT product_name, price
        FROM products
        ORDER BY price DESC
    ) a WHERE ROWNUM <= 20  -- Upper bound (offset + limit)
) WHERE rn > 10;  -- Lower bound (offset)
 
-- Modern pagination: Clean and readable
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

Cross-Database Syntax Comparison

When writing portable SQL or migrating between databases, understanding syntax equivalences is crucial. Here's a comprehensive comparison:

Row Limiting Syntax by Database
Database	Primary Syntax	Also Supports	Notes
MySQL	LIMIT n		LIMIT must be literal in older versions
PostgreSQL	LIMIT n	FETCH FIRST	LIMIT predates standard adoption
SQLite	LIMIT n		LIMIT only, no FETCH support
SQL Server 2012+	TOP (n)	OFFSET FETCH	TOP for backward compat
SQL Server (older)	TOP (n)		No FETCH FIRST support
Oracle 12c+	FETCH FIRST	ROWNUM	ROWNUM for legacy code
Oracle (older)	ROWNUM		Requires subquery tricks
DB2	FETCH FIRST		Standard compliant
MariaDB	LIMIT n		MySQL-compatible

5.1 Equivalent Queries Across Databases

The same logical query ('top 10 products by price') in each syntax:

top10_mysql.sql

-- MySQL: LIMIT clause
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

5.2 Portability Strategies

When writing SQL that needs to work across multiple databases:

Cross-Database Portability Approaches

•Use FETCH FIRST for new projects — It's the SQL standard and supported by most modern database versions. Future-proof choice.
•Abstract at the application layer — Use an ORM or query builder that generates appropriate syntax for each database.
•Create database-specific views — Encapsulate limiting logic in views that differ per database, with consistent view names.
•Document database assumptions — If using LIMIT for simplicity, document the MySQL/PostgreSQL dependency clearly.
•Test on all target databases — Syntax acceptance doesn't guarantee identical behavior. Test edge cases on each platform.

Performance Considerations

6.1 Top-N Query Optimization

When you want the first N rows by some ordering, and an index exists on the ORDER BY column, the database can use a top-N sort optimization:

topn_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Index on products(price)
CREATE INDEX idx_price ON products(price DESC);
 
-- Highly optimized query
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Execution:
-- 1. Uses index to read products in price DESC order
-- 2. Stops after 10 rows (no full table scan!)
-- 3. Execution time: constant regardless of table size
 
-- Compare without index or with non-indexed ORDER BY:
SELECT product_name, price
FROM products
ORDER BY RANDOM()  -- Can't use index
LIMIT 10;
 
-- Execution:
-- 1. Full table scan (reads all rows)
-- 2. Sorts ALL rows randomly
-- 3. Returns first 10
-- 4. Execution time: proportional to table size

6.2 When LIMIT Cannot Optimize

LIMIT cannot reduce work in several scenarios:

LIMIT Optimization Barriers

•Aggregation before LIMIT — SELECT SUM(x) LIMIT 10 still computes the full sum before limiting (sum returns 1 row anyway).
•DISTINCT before LIMIT — SELECT DISTINCT x LIMIT 10 may need to scan many rows to find 10 unique values.
•Non-indexed ORDER BY — Without an index, ALL rows must be sorted to find the top N.
•Complex WHERE filters — Even with LIMIT 1, if no index supports the WHERE clause, a full scan may occur.
•Joins that multiply rows — If a join produces 10 million rows before LIMIT 10 is applied, significant work occurs before limiting.

limit_barriers.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- LIMIT cannot optimize these queries:
 
-- Aggregation: Must process all rows for sum
SELECT department, SUM(salary) as total
FROM employees
GROUP BY department
ORDER BY total DESC
LIMIT 5;
-- Must compute SUM for ALL departments first
 
-- DISTINCT with low cardinality: May scan many rows
SELECT DISTINCT status
FROM orders
LIMIT 3;
-- If there are only 4 statuses spread across 10M orders,
-- might scan millions to find 3 unique values
 
-- Non-indexed ORDER BY
SELECT * FROM large_table ORDER BY non_indexed_column LIMIT 10;
-- Full table scan + full sort, then return 10
 
-- Optimize with covering index:
CREATE INDEX idx_covering ON large_table(non_indexed_column) 
INCLUDE (other_columns);

6.3 EXPLAIN Analysis

Always use EXPLAIN to verify that LIMIT is being used effectively:

limit_explain.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- PostgreSQL EXPLAIN analysis
EXPLAIN (ANALYZE, BUFFERS) 
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- Good plan indicator:
-- Limit  (cost=0.42..0.68 rows=10)
--   ->  Index Scan Backward using idx_price on products (rows=10)
-- Shows: Index scan, low row count, stopped early
 
-- Bad plan indicator:
-- Limit  (cost=15000..15001 rows=10)
--   ->  Sort  (cost=15000..16000 rows=100000)
--     ->  Seq Scan on products (rows=100000)
-- Shows: Full scan, full sort, then limit (expensive)
 
-- SQL Server equivalent
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;
-- Check for 'logical reads' - lower is better

Index Design for Top-N

Common LIMIT/TOP Use Cases

Row limiting is fundamental to many application patterns. Here are the most common use cases:

limit_use_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Use Case 1: Existence Check (is there at least one?)
SELECT 1 FROM orders WHERE customer_id = 123 LIMIT 1;
-- Fast existence check - stops at first match
-- Returns 1 row if exists, 0 if not
 
-- Use Case 2: Get Single Record by ID
SELECT * FROM users WHERE user_id = 456 LIMIT 1;
-- Even with unique ID, LIMIT 1 can hint the optimizer
 
-- Use Case 3: Most Recent Record
SELECT * FROM audit_log 
WHERE entity_id = 789 
ORDER BY created_at DESC 
LIMIT 1;
-- Get the latest audit entry for an entity
 
-- Use Case 4: Top N List
SELECT player_name, score
FROM game_scores
WHERE game_date = CURRENT_DATE
ORDER BY score DESC
LIMIT 10;
-- Daily leaderboard top 10
 
-- Use Case 5: Random Sample
SELECT * FROM products
ORDER BY RANDOM()  -- PostgreSQL/SQLite
LIMIT 5;
-- Random 5 products (expensive for large tables!)
 
-- SQL Server random sample:
SELECT TOP (5) * FROM products ORDER BY NEWID();
 
-- Use Case 6: Data Preview
SELECT * FROM large_import_table LIMIT 100;
-- Quick preview of imported data structure/content
 
-- Use Case 7: Batch Processing Loop
-- Fetch batches of 1000 for processing
SELECT * FROM pending_jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at ASC
LIMIT 1000;
-- Process 1000, mark complete, repeat
 
-- Use Case 8: Pagination (covered in detail next page)
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;  -- Page 3 of 20 items per page

Existence Checks: EXISTS vs. LIMIT 1

For existence checks, both SELECT 1 WHERE EXISTS (subquery) and subquery LIMIT 1 work. EXISTS is generally preferred as it's semantically clearer and can never accidentally return multiple rows.

Summary: Row Limiting Across Databases

Row limiting is an essential SQL skill, complicated by the variety of syntaxes across database systems. Let's consolidate the key takeaways:

Key Takeaways

•Three main syntaxes exist — LIMIT (MySQL, PostgreSQL, SQLite), TOP (SQL Server), and FETCH FIRST (SQL standard). Know which your database uses.
•FETCH FIRST is the SQL standard — For new, portable code, prefer the SQL:2008 syntax: FETCH FIRST n ROWS ONLY. It's widely supported in modern database versions.
•Always use ORDER BY with limiting — Without ORDER BY, which rows are returned is undefined and non-deterministic. Results may vary between executions.
•WITH TIES handles equal rankings — Use it when arbitrary cutoffs are unacceptable (leaderboards, thresholds). Omit it when you need exactly N rows.
•TOP supports PERCENT — SQL Server's TOP (n) PERCENT returns a fraction of rows—useful for sampling.
•ROWNUM is Oracle legacy — It works but requires subqueries for ordered limiting. Migrate to FETCH FIRST on Oracle 12c+.
•Performance depends on indexes — With proper indexes, top-N queries are extremely fast (read only N rows). Without indexes, full scans may occur despite LIMIT.
•LIMIT is applied last — After WHERE, GROUP BY, ORDER BY. It limits the final result set, not intermediate processing.

What's Next:

Page Complete

3 / 5