SQL - Select first 10 rows only?

Question

Hi,

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

I would like to display the first 10 results only 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

Thanks!

Answer 1

Depends on your RDBMS

MS SQL Server

SELECT TOP 10 ...

MySQL

SELECT ... LIMIT 10

Sybase

SET ROWCOUNT 10
SELECT ...

Etc.

Answer 2

In SQL server, use:

select top 10 ...

In MySQL, use:

select ... order by num desc limit 10

Answer 3

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

Answer 4

DB2

... FETCH FIRST 10 ROWS ONLY

Answer 5

Oracle

WHERE ROWNUM <= 10  and whatever_else ;

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