CODEWITHSUNDEEP
sql
Mike
Mike

Reputation: 4301

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

Upvotes: 178

Views: 1065968

Answers (12)

Derek Slager
Derek Slager

Reputation: 13851

In SQL Server, use:

select top 10 ...

e.g.

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

In MySQL, use:

select ... order by num desc limit 10

Upvotes: 249

jarlh
jarlh

Reputation: 44805

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 ROWS
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, MariaDB and DB2 etc.

Note that with SQL Server, you must always specify OFFSET when using FETCH FIRST. (Should be optional according to the ANSI/ISO SQL standard.)

Upvotes: 42

martin clayton
martin clayton

Reputation: 78225

Depends on your RDBMS

MS SQL Server

SELECT TOP 10 ...

MySQL and PostgreSQL 8.3 or older:

SELECT ... LIMIT 10

Sybase (>v15 ASE support TOP too)

SET ROWCOUNT 10
SELECT ...

Etc.

Upvotes: 77

brabster
brabster

Reputation: 43610

In standard SQL you can use:

... FETCH FIRST 10 ROWS ONLY

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

Upvotes: 29

Henry
Henry

Reputation: 1337

PostgreSQL:

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

Upvotes: 3

Code
Code

Reputation: 749

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

Upvotes: 0

ne2dmar
ne2dmar

Reputation: 534

Firebird:

SELECT FIRST 10 * FROM MYTABLE

Upvotes: 2

kiran kumar
kiran kumar

Reputation: 39

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

Upvotes: 0

AvatarKava
AvatarKava

Reputation: 15453

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

Upvotes: 3

sayannayas
sayannayas

Reputation: 774

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

Upvotes: 3

Ben
Ben

Reputation: 3042

In MySQL:

SELECT * FROM `table` LIMIT 0, 10

Upvotes: 35

wallyk
wallyk

Reputation: 57804

Oracle

WHERE ROWNUM <= 10  and whatever_else ;

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

Upvotes: 23

Related Questions