How to Limit SQL Query Results to the Top Rows?

Select first few rows in SQL query: Result limit

In database queries, it is often necessary to display only a limited number of results. Whether due to page limitations or performance optimization, knowing how to select only the first few specific rows can significantly enhance database operations.

For example, consider the following query, which retrieves information about celebrities mentioned in posts over the past day:

<code class="language-sql">SELECT a.names,
         COUNT(b.post_title) AS num
    FROM wp_celebnames a
    JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0
    WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY a.names
ORDER BY num DESC</code>

To display only the first 10 results for this query, you can use a database-specific method to achieve this:

SQL Server:

In SQL Server, you can use the TOP keyword to specify the number of rows to retrieve. The syntax is as follows:

<code class="language-sql">SELECT TOP [number] ...</code>

For example, to select the top 10 rows from a given query, use:

<code class="language-sql">SELECT TOP 10 a.names,
         COUNT(b.post_title) AS num
    FROM wp_celebnames a
    JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0
    WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY a.names
ORDER BY num DESC</code>

MySQL:

In MySQL, you can use the LIMIT clause to limit the number of rows returned. The syntax is as follows:

<code class="language-sql">... ORDER BY [column_name] [order] LIMIT [number]</code>

For example, to select the top 10 rows from a given query, use:

<code class="language-sql">SELECT a.names,
         COUNT(b.post_title) AS num
    FROM wp_celebnames a
    JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0
    WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY a.names
ORDER BY num DESC
LIMIT 10</code>

The above is the detailed content of How to Limit SQL Query Results to the Top Rows?. For more information, please follow other related articles on the PHP Chinese website!