SQL - Select first 10 rows only?

user_225487 0
Viewed 885533

How do I select only the first 10 results of a query?

I would like to display only the first 10 results from the following query:

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
sql
12 Answers

In SQL server, use:

select top 10 ...

e.g. 

select top 100 * from myTable
select top 100 colA, colB from myTable

In MySQL, use:

select ... order by num desc limit 10
user_186360

Depends on your RDBMS

MS SQL Server

SELECT TOP 10 ...

MySQL

SELECT ... LIMIT 10

Sybase

SET ROWCOUNT 10
SELECT ...

Etc.

user_1831720

In standard SQL you can use:

... FETCH FIRST 10 ROWS ONLY

This is supported in DB2, PostgreSQL and Oracle 12.1 (and later)

Brabster_23620

Oracle

WHERE ROWNUM <= 10  and whatever_else ;

ROWNUM is a magic variable which contains each row's sequence number 1..n.

user_1985360

What you're looking for is a LIMIT clause.

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
user_586150

PostgreSQL:

SELECT ... LIMIT [num] OFFSET [num];
user_30545110 
SELECT  Top(12) Month, Year, Code FROM TempEmp 
ORDER BY  Year DESC,month DESC
user_97871730
Related