đŸ‡ŻđŸ‡” æ—„æœŹèȘž | đŸ‡ș🇾 English | đŸ‡Ș🇾 Español | đŸ‡”đŸ‡č PortuguĂȘs | đŸ‡č🇭 àč„àž—àžą | 🇹🇳 äž­æ–‡

[SQL] Mastering the LIMIT Clause: Control Displayed Rows from Basics to Pagination

“Top 5 New Arrivals,” “Top 10 Popular Articles,” “First Page of Search Results”
 On websites and apps, it's very common to display only a portion of a large data set. The powerful SQL clause that makes this possible is LIMIT.

This article is aimed at web creators and explains everything from the basics of the LIMIT clause to implementing the essential web development technique of pagination. It includes rich sample code and even an interactive execution environment you can copy and paste. Try it hands-on and equip yourself with this powerful tool to manipulate data smartly!

Warm-up First! We'll Use Sample Data Again

Before diving in, we’ll once again use the product table (products) from a fictional online store called “Web Creator's Mart.” If you've read our previous ORDER BY article, it may look familiar to you.

The SQL below creates the table and inserts data. You can try this in your environment or use it in the “interactive execution environment” we’ll show later. Take a moment to review it.

-- Create product table

CREATE TABLE products (

  id INT,

  name VARCHAR(255),

  category VARCHAR(255),

  price INT,

  release_date DATE

);



-- Insert sample data (7 rows)

INSERT INTO products (id, name, category, price, release_date) VALUES

(1, 'Fluffy Cushion', 'Interior', 3200, '2023-04-15'),

(2, 'Focus Ear Muffs', 'Gadget', 7800, '2023-11-01'),

(3, 'Idea Spark Notebook', 'Stationery', 1200, '2022-08-20'),

(4, 'Glowing Keyboard', 'Gadget', 15000, '2023-06-30'),

(5, 'Ultimate Comfort Chair', 'Interior', 32000, '2022-11-10'),

(6, 'Smooth Ballpoint Pen', 'Stationery', 1200, '2023-09-01'),

(7, 'Creator Mug Cup', 'Tableware', 2500, '2023-04-15');

All set? Let’s jump into the world of the LIMIT clause!

Basics of the LIMIT Clause: Specify the Number of Rows to Retrieve

The simplest use of the LIMIT clause is to specify the maximum number of records (rows) to retrieve. The syntax is very simple — just add LIMIT number at the end of your SELECT statement.

For example, let’s try retrieving just 3 rows from the products table.

SELECT * FROM products LIMIT 3;

When executed, this returns the first 3 rows in the order they are stored in the table. It's very intuitive. However, there's one important caveat — without any ordering, there’s no guarantee on which 3 rows you’ll get. The order may vary each time depending on the internal behavior of the database.

The true power of the LIMIT clause is unleashed when combined with ORDER BY, which we covered in the previous article.

Power Combo! Use ORDER BY + LIMIT to Get the “Top N” Rows

Getting the “Top N rows” — such as “Top 3 most expensive products” or “Top 5 newest releases” — is a common requirement on websites. You can achieve this by first sorting the data using ORDER BY and then limiting the results with LIMIT.

Let’s retrieve the top 3 most expensive products. Use ORDER BY price DESC to sort in descending order of price, and then apply LIMIT 3 to get the top 3.

-- Get the top 3 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 3;

How did it go? You should see “Ultimate Comfort Chair”, “Glowing Keyboard”, and “Focus Ear Muffs” in that order. The key pattern here is to use ORDER BY to determine the order, then apply LIMIT to trim it. Remember to always use them together.

Let’s also get the “Top 2 newest products”. This time, we sort by release date (release_date).

-- Get the top 2 newest products by release date
SELECT * FROM products ORDER BY release_date DESC LIMIT 2;

Shift the Starting Point with OFFSET

The LIMIT clause has a powerful partner — OFFSET. As the name suggests, OFFSET specifies “how many rows to skip from the beginning”.

For example, suppose you want to exclude the top product and fetch only the 2nd and 3rd most expensive items. OFFSET makes this possible.

-- Skip the first result and retrieve the next 2 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 2 OFFSET 1;

This SQL works as follows:

  1. ORDER BY price DESC sorts the products in descending price order.
  2. OFFSET 1 skips the first product (the most expensive).
  3. LIMIT 2 fetches the next 2 items (2nd and 3rd).

This combination of LIMIT and OFFSET is at the heart of the “pagination” technique we’ll explore next.

Advanced: Implement Pagination Using LIMIT and OFFSET

Pagination is a technique used to split large data sets — such as blog posts or product lists — across multiple pages. You’ve probably seen pagers like “1, 2, 3, 4, 5, ... Next” on many websites.

Pagination is implemented using LIMIT and OFFSET. Let’s say you want to show 3 items per page.

The SQL for each page looks like this. To keep the order consistent, we’ll sort by id.

【SQL for Page 1】

-- Page 1: skip 0 rows, get 3
SELECT * FROM products ORDER BY id LIMIT 3 OFFSET 0;

【SQL for Page 2】

-- Page 2: skip 3 rows, get 3
SELECT * FROM products ORDER BY id LIMIT 3 OFFSET 3;

【SQL for Page 3】

-- Page 3: skip 6 rows, get 3
SELECT * FROM products ORDER BY id LIMIT 3 OFFSET 6;

Notice something? The LIMIT value (items per page) is always 3. The OFFSET value increases as 0, 3, 6, .... You can calculate the OFFSET with:

OFFSET = (current page - 1) × items per page

By implementing this logic in your program, you can build dynamic pagination easily. And don’t worry — in the next section, we’ll show you a live interactive demo!

【Try it out!】Execute SQL directly in your browser

Thank you for waiting! We have prepared an interactive environment where you can run SQL directly in your browser. With this, you can try out various LIMIT clause patterns discussed in this article by yourself.

Copy the entire code below, save it as a file such as index.html, and open it in your browser. Paste the sample SQL from this article into the text area and click the “Run SQL” button!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL Practice Environment</title>
  <style>
    body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.6; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1, h2 { color: #444; border-bottom: 2px solid #eee; padding-bottom: 0.3em;}
    textarea { width: 100%; height: 150px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
    button:hover { background-color: #0056b3; }
    #result-container { margin-top: 1.5rem; min-height: 100px;}
    table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
    th { background-color: #f8f8f8; }
    #error-message { color: red; font-weight: bold; margin-top: 1rem; }
  </style>
</head>
<body>

  <h1>Learn by running SQL! LIMIT Clause Practice</h1>
  <p>Enter an SQL statement in the text area below and click the “Run SQL” button. Try out the various SQL examples introduced in the article!</p>

  <textarea id="sql-input">-- Paste SQL here and run!
-- Example: Top 3 most expensive products
SELECT * FROM products ORDER BY price DESC LIMIT 3;</textarea>
  <button onclick="executeQuery()">Run SQL</button>

  <h2>Execution Result</h2>
  <div id="result-container"></div>
  <div id="error-message"></div>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
  <script>
    let db;

    async function initDb() {
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        const setupSQL = `
          CREATE TABLE products (id INT, name VARCHAR(255), category VARCHAR(255), price INT, release_date DATE);
          INSERT INTO products (id, name, category, price, release_date) VALUES
          (1, 'Fluffy Cushion', 'Interior', 3200, '2023-04-15'),
          (2, 'Noise-Cancelling Earmuffs', 'Gadget', 7800, '2023-11-01'),
          (3, 'Idea Spark Notebook', 'Stationery', 1200, '2022-08-20'),
          (4, 'Glowing Keyboard', 'Gadget', 15000, '2023-06-30'),
          (5, 'Ultimate Comfort Chair', 'Interior', 32000, '2022-11-10'),
          (6, 'Smooth Ballpoint Pen', 'Stationery', 1200, '2023-09-01'),
          (7, 'Creator’s Mug Cup', 'Tableware', 2500, '2023-04-15');
        `;
        db.run(setupSQL);
        executeQuery(); // Run initial SQL
      } catch (e) {
        document.getElementById('error-message').textContent = 'Failed to initialize the database: ' + e.message;
      }
    }

    function executeQuery() {
      const sqlInput = document.getElementById('sql-input').value;
      const resultContainer = document.getElementById('result-container');
      const errorMessage = document.getElementById('error-message');
      
      resultContainer.innerHTML = '';
      errorMessage.innerHTML = '';

      try {
        const results = db.exec(sqlInput);
        if (results.length > 0) {
          resultContainer.appendChild(createTable(results[0]));
        } else {
          resultContainer.innerHTML = '<p>Query executed successfully, but no result set was returned. (e.g., CREATE TABLE, INSERT)</p>';
        }
      } catch (e) {
        errorMessage.textContent = 'Error: ' + e.message;
      }
    }

    function createTable(result) {
      const table = document.createElement('table');
      const thead = document.createElement('thead');
      const tbody = document.createElement('tbody');
      const headerRow = document.createElement('tr');
      result.columns.forEach(colName => {
        const th = document.createElement('th');
        th.textContent = colName;
        headerRow.appendChild(th);
      });
      thead.appendChild(headerRow);
      result.values.forEach(rowData => {
        const tr = document.createElement('tr');
        rowData.forEach(cellData => {
          const td = document.createElement('td');
          td.textContent = cellData;
          tr.appendChild(td);
        });
        tbody.appendChild(tr);
      });
      table.appendChild(thead);
      table.appendChild(tbody);
      return table;
    }

    initDb();
  </script>
</body>
</html>

Supplement: SQL Dialect Differences by Database

The LIMIT ... OFFSET ... syntax we explained in this article is a standard format supported by many databases such as MySQL, PostgreSQL, and SQLite. However, some databases use different syntax (dialects), so we’ll briefly introduce them here for reference.

For SQL Server

Microsoft SQL Server used to rely on the TOP clause in older versions, but in recent versions, the OFFSET ... FETCH ... clause is recommended.

-- Pagination in SQL Server (skip 3 rows and fetch the next 3)
SELECT * FROM products
ORDER BY id
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

For Oracle

Oracle Database also uses the OFFSET ... FETCH ... syntax, similar to SQL Server.

-- Pagination in Oracle (skip 3 rows and fetch the next 3)
SELECT * FROM products
ORDER BY id
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

As you can see, the syntax may vary depending on the database, but the underlying concept—specifying how many records to skip and how many to retrieve—is exactly the same.

Summary: Mastering LIMIT is Key to Mastering Data Display!

In this article, we explored the SQL LIMIT clause and its counterpart OFFSET, from the basics to advanced usage. Let’s recap the key takeaways:

By retrieving only the necessary data in the necessary amount, the LIMIT clause helps maintain database performance and provides users with a smoother UI experience. Try out various scenarios in the demo environment and fully master its behavior!