SQL - Select first 10 rows only?

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.

SQL - Select first 10 rows only?

Related

Recent Posts