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:

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

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:

SELECT TOP [number] ...

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

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

MySQL:

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

... ORDER BY [column_name] [order] LIMIT [number]

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

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

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!