SQL - Select first 10 rows only?

Question

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

Should be the new answer, https://stackoverflow.com/a/36476971/124486 — Evan Carroll, Jan 07 '18 at 00:32

score 236 · Accepted Answer · edited Jul 20 '17 at 01:13

236

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

edited Jul 20 '17 at 01:13

Vicky

16,679
54
139
232

answered Dec 12 '09 at 00:55

Derek Slager

13,619
3
34
34

score 66 · Answer 2 · answered Dec 12 '09 at 00:54

66

Depends on your RDBMS

MS SQL Server

SELECT TOP 10 ...

MySQL

SELECT ... LIMIT 10

Sybase

SET ROWCOUNT 10
SELECT ...

Etc.

answered Dec 12 '09 at 00:54

martin clayton

76,436
32
213
198

8

The ... LIMIT 10 syntax is also used by sqlite3 – dat Dec 14 '13 at 03:17

jarlh · Answer 3 · 2021-08-31T15:03:32.233

38

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.

edited Aug 31 '21 at 15:03

answered Apr 07 '16 at 12:59

jarlh

42,561
8
45
63

1

Just to make it clearer: The first query is not supported by MS SQL. It only works with the usage of the offset. – Alexander Heim Oct 14 '21 at 07:33

score 34 · Answer 4 · answered Oct 02 '12 at 02:42

34

In MySQL:

SELECT * FROM `table` LIMIT 0, 10

answered Oct 02 '12 at 02:42

Ben

3,012
1
21
25

3

What's the difference between limit 0, 10 and limit 10? And why doesn't limit 10, 20 give me 10 rows between the 10th and 20th rows? EDIT: Oh, so limit 10, 20 means give me 20 rows after the 10th row. If I want rows between 10 and 20 I have to do limit 10, 10. Thanks! – AbdurRehman Khan Aug 01 '19 at 12:26

score 28 · Answer 5 · edited Feb 19 '20 at 07:38

28

In standard SQL you can use:

... FETCH FIRST 10 ROWS ONLY

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

edited Feb 19 '20 at 07:38

answered Dec 12 '09 at 00:55

brabster

42,504
27
146
186

for PostgreSQL too – ralf.w. Sep 04 '17 at 10:42

score 22 · Answer 6 · answered Dec 12 '09 at 00:59

22

Oracle

WHERE ROWNUM <= 10  and whatever_else ;

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

answered Dec 12 '09 at 00:59

wallyk

56,922
16
83
148

3

This is wrong. "rownum" will fetch the top 'n' rows from the table. It will not filter from "whatever_else" condition! – Coder Jun 06 '14 at 10:58
1

http://stackoverflow.com/questions/874082/show-only-the-first-n-lines-of-output-of-a-sql-query Please follow the answer by stili for Oracle related DB – Coder Jun 06 '14 at 11:08

score 3 · Answer 7 · answered Oct 28 '12 at 13:18

3

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

answered Oct 28 '12 at 13:18

sayannayas

764
9
15

score 3 · Answer 8 · edited Jul 07 '13 at 05:51

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

edited Jul 07 '13 at 05:51

Ali Almoullim

1,028
9
30

answered Dec 12 '09 at 00:55

AvatarKava

15,245
2
27
33

score 3 · Answer 9 · answered Sep 02 '19 at 05:38

3

PostgreSQL:

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

answered Sep 02 '19 at 05:38

Henry

1,077
1
16
41

2

Limit is first answer but problem with Limit is, if you have 100,000 record and you want to fetch 10 with limit, it will still take whole time to fetch 100,000 record then will apply limit. so this way sql query get slower for larger record. thats why we are looking for alternate of limit. – Dheeraj Thedijje Aug 21 '20 at 16:19

score 2 · Answer 10 · edited Nov 30 '15 at 14:09

2

Firebird:

SELECT FIRST 10 * FROM MYTABLE

edited Nov 30 '15 at 14:09

JNYRanger

6,829
12
53
81

answered Feb 12 '15 at 13:05

ne2dmar

504
4
17

score 0 · Answer 11 · answered Jan 10 '19 at 13:02

0

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

answered Jan 10 '19 at 13:02

Code

679
5
9

score -1 · Answer 12 · edited Nov 30 '15 at 14:08

-1

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

edited Nov 30 '15 at 14:08

JNYRanger

6,829
12
53
81

answered Nov 30 '15 at 13:45

kiran kumar

29

according to here: https://www.w3schools.com/sql/sql_top.asp This is a listed answer for Oracle.. Up vote. – eaglei22 May 08 '17 at 16:12
2

This will get you 10 rows, but if you have an "order by" clause, it won't work how you expect. rownum filtering happens before any order by or group by clauses, so you'll get 10 rows, which will then be grouped, or then be ordered. rather than grouping the rows, ordering them how you expect and then giving you first 10 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726 – pohart Feb 14 '23 at 17:15

SQL - Select first 10 rows only?

12 Answers12

Linked

Related