SQL - Select first 10 rows only?
SqlSql Problem Overview
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
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
Depends on your RDBMS
MS SQL Server
SELECT TOP 10 ...
MySQL
SELECT ... LIMIT 10
Sybase
SET ROWCOUNT 10
SELECT ...
Etc.
Solution 3 - Sql
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.
Solution 4 - Sql
In MySQL:
SELECT * FROM `table` LIMIT 0, 10
Solution 5 - Sql
In standard SQL you can use:
... FETCH FIRST 10 ROWS ONLY
This is supported in DB2, PostgreSQL and Oracle 12.1 (and later)
Solution 6 - Sql
Oracle
WHERE ROWNUM <= 10 and whatever_else ;
ROWNUM is a magic variable which contains each row's sequence number 1..n.
Solution 7 - Sql
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
Solution 8 - Sql
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
Solution 9 - Sql
PostgreSQL:
SELECT ... LIMIT [num] OFFSET [num];
Solution 10 - Sql
Firebird:
SELECT FIRST 10 * FROM MYTABLE
Solution 11 - Sql
SELECT* from <table name> WHERE rownum <= 10;
Solution 12 - Sql
SELECT Top(12) Month, Year, Code FROM TempEmp
ORDER BY Year DESC,month DESC