Sql - Select First 10 Rows Only?

sql

limit

offset

fetch-first

byAnton Shumikhin·Oct 12, 2024

The easiest way to retrieve just the first 10 rows from your query result is to append LIMIT 10 in MySQL, PostgreSQL, and SQLite systems. For SQL Server, it's as simple as adding TOP 10 at the beginning. Use the ORDER BY clause to ensure sorted and predictable results.

Cross-Platform Examples:

-- MySQL, PostgreSQL, SQLite
SELECT * FROM your_table ORDER BY column_name LIMIT 10;  -- Just the top 10, please!

-- SQL Server
SELECT TOP 10 * FROM your_table ORDER BY column_name;  -- Let's keep it short!

Handling order of rows

Including an ORDER BY clause becomes crucial when the sequence of the data is essential. You usually need this when working with time-sensitive data or when you want to retrieve the highest or lowest data entities.

-- SQL Server
SELECT TOP 10 * FROM sales_data ORDER BY transaction_date DESC;  -- Latest gossip... I mean sales!

-- MySQL
SELECT * FROM sales_data ORDER BY transaction_date DESC LIMIT 10;  -- Only the freshest!

This ensures the most recent sales come first, hence returning the most relevant results.

Pagination, ties, and other tricks

We use OFFSET with FETCH FIRST for pagination, and when dealing with tied values, FETCH FIRST will come into play.

Pagination, or resulting data into pages, can be achieved by:

-- SQL Server with "OFFSET" and "FETCH NEXT"
SELECT * FROM products ORDER BY price OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- Page 3 anyone?

--MySQL
SELECT * FROM products ORDER BY price LIMIT 20, 10;  -- Still on Page 3, hold on!

For ties, where values have the same rank, we handle them as follows:

-- SQL Server to include ties
SELECT * FROM employees ORDER BY salary FETCH FIRST 10 ROWS WITH TIES;  -- If they earn the same, they're the same!

Counting, filtering and joining--in order!

When counting the number of occurrences or joining multiple tables together, remember this: filtering (WHERE) → ordering (ORDER BY) → counting/ joining → then finally selecting the limited rows (LIMIT/ TOP).

Trying to count tags in posts? Make sure you have the most frequent ones!

-- MySQL attempt at counting
SELECT tag, COUNT(*) as frequency
FROM posts_tags
GROUP BY tag
ORDER BY frequency DESC
LIMIT 10;  -- The top 10 trend setters!

Joining tables? Ensure accurate matching or else you'll be mixing apples with oranges!

-- SQL Server joining tables in style
SELECT TOP 10 p.name, o.quantity
FROM products p
JOIN orders o ON p.product_id = o.product_id
ORDER BY o.quantity DESC;  -- Only the best-sellers!

Non-discriminatory database systems

FETCH FIRST is a modern SQL standard accepted by various DBMS including DB2, PostgreSQL, and Oracle. Each system, however, has its syntax specialities. For instance, in Oracle 12.1 and later, you would:

SELECT * FROM products ORDER BY price
FETCH FIRST 10 ROWS ONLY;  -- Oracle knows best!

While in Sybase ASE, the results are limited as follows:

SET ROWCOUNT 10
SELECT * FROM products ORDER BY price
SET ROWCOUNT 0  -- No more limits, sky's the limit!

Before LIMIT comes WHERE

Please, before applying LIMIT, use WHERE to trim down your dataset. It makes your query more efficient, especially when dealing with big data. Retrieve only the most relevant rows!

-- Efficient MySQL filtering before limiting
SELECT * FROM transactions
WHERE transaction_date > CURRENT_DATE - INTERVAL 1 DAY
ORDER BY amount DESC 
LIMIT 10;  -- Only interested in fresh deals!