SQL - Select first 10 rows only?

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 

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 

Depends on your RDBMS

MS SQL Server

SELECT TOP 10 ... 

MySQL

SELECT ... LIMIT 10 

Sybase

SET ROWCOUNT 10 SELECT ... 

Etc.

The ANSI SQL answer is FETCH FIRST.

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 FETCH FIRST 10 ROWS ONLY 

If you want ties to be included, do FETCH FIRST 10 ROWS WITH TIES instead.

To skip a specified number of rows, use OFFSET, e.g.

... ORDER BY num DESC OFFSET 20 FETCH FIRST 10 ROWS ONLY 

Will skip the first 20 rows, and then fetch 10 rows.

Supported by newer versions of Oracle, PostgreSQL, MS SQL Server, Mimer SQL and DB2 etc.

In MySQL:

SELECT * FROM `table` LIMIT 0, 10 

In standard SQL you can use:

... FETCH FIRST 10 ROWS ONLY

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

Oracle

WHERE ROWNUM <= 10 and whatever_else ; 

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

SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY user_id) user_row_no, a.* FROM temp_emp a) WHERE user_row_no > 1 and user_row_no <11 

This worked for me.If i may,i have few useful dbscripts that you can have look at

Useful Dbscripts

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 

PostgreSQL:

SELECT ... LIMIT [num] OFFSET [num]; 

Firebird:

SELECT FIRST 10 * FROM MYTABLE 

SELECT* from <table name> WHERE rownum <= 10; 

SELECT Top(12) Month, Year, Code FROM TempEmp ORDER BY Year DESC,month DESC