Stack Overflow
Stack Overflow

Questions tagged [sql-limit]

SQL clause to limit number of returned rows

The LIMIT clause is used to specify a maximum of rows to be returned in a SELECT query. The various SQL dialects use different syntax elements for that purpose. LIMIT x OFFSET y, is understood by MySQL, PostgreSQL, SQLite and some others RDBMS.
Example:

SELECT * FROM tbl LIMIT 10;

The SQL:2008 standard defines:

FETCH FIRST n ROWS ONLY

SQL Server uses TOP n, Oracle its rownum feature.
There is a comprehensive list on Wikipedia.

315 questions
1310
votes
15 answers

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are…
Mathieu Longtin
  • 15,922
  • 6
  • 30
  • 40
446
votes
4 answers

How to get the top 10 values in postgresql?

I have simple question: I have a postgresql table: Scores(score integer). How would I get the highest 10 scores the fastest? UPDATE: I will be doing this query multiple times and am aiming for the fastest solution.
Joey Franklin
  • 6,423
  • 7
  • 25
  • 22
292
votes
9 answers

How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?
Gold
  • 60,526
  • 100
  • 215
  • 315
289
votes
8 answers

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by the publish_date field? If it's the…
Alex
  • 66,732
  • 177
  • 439
  • 641
165
votes
3 answers

Sqlite LIMIT / OFFSET query

I have simple question with Sqlite. What is the difference between this: Select * from Animals LIMIT 100 OFFSET 50 and Select * from Animals LIMIT 100,50
Pablo
  • 1,651
  • 2
  • 11
  • 3
145
votes
4 answers

Is there a way to "limit" the result with ELOQUENT ORM of Laravel?

Is there a way to "limit" the result with ELOQUENT ORM of Laravel? SELECT * FROM `games` LIMIT 30 , 30 And with Eloquent ?
Natan Shalva
  • 1,572
  • 2
  • 11
  • 11
132
votes
10 answers

MySQL offset infinite rows

I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an offset. Is there any way to do this?
stillinbeta
  • 1,977
  • 3
  • 17
  • 23
93
votes
6 answers

How to limit rows in PostgreSQL SELECT

What's the equivalent to SQL Server's TOP or DB2's FETCH FIRST or mySQL's LIMIT in PostgreSQL?
Dan Mertz
  • 957
  • 1
  • 6
  • 4
89
votes
5 answers

Best way to get result count before LIMIT was applied

When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls. Currently I do that by running the query twice, once wrapped in a count() to determine the total results, and a second…
EvilPuppetMaster
  • 8,072
  • 10
  • 34
  • 31
79
votes
1 answer

MySQL COUNT with LIMIT

What I want to do is SUM a column, but also COUNT the number of rows it is summing, with a limit of no more than 5 rows. So my query is: SELECT COUNT(*), SUM(score) FROM answers WHERE user=1 LIMIT 5 What I expected back was a COUNT(*) up to 5 (I…
Lee
  • 10,496
  • 4
  • 37
  • 45
61
votes
4 answers

Alternatives to LIMIT and OFFSET for paging in Oracle

I'm developing a web application and need to page ordered results. I normaly use LIMIT/OFFSET for this purpose. Which is the best way to page ordered results in Oracle? I've seen some samples using rownum and subqueries. Is that the way? Could you…
danielpradilla
  • 827
  • 1
  • 8
  • 15
32
votes
1 answer

SQL (ORACLE): ORDER BY and LIMIT

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET. Query like this: SELECT select_list FROM table_expression [ ORDER BY ... ] [ LIMIT { number | ALL } ] [ OFFSET number ] I know the sorting ends…
DraggonZ
  • 1,057
  • 1
  • 16
  • 22
32
votes
4 answers

How to limit returned results of a JOIN query in MySQL

I am trying to limit the following SQL statement. SELECT expense.*, transaction.* FROM expense INNER JOIN transaction ON expense_id = transaction_expense_id What I want to do, is limit the number of 'parent' rows. IE. if I do a LIMIT 1, I would…
Thomas R
  • 3,026
  • 5
  • 32
  • 31
27
votes
5 answers

MySQL limit range

SELECT name FROM mydb ORDER BY score DESC LIMIT 10; The query above will return the first 10 ranks. How to modify the LIMIT, or maybe is there another syntax to query the 10th rank through the 20th rank?
theHack
  • 1,938
  • 9
  • 26
  • 33
22
votes
3 answers

Any point in using LIMIT in EXISTS query?

Is there any performance benefit in adding a LIMIT to an EXISTS query, or would MySQL apply the limit on its own? Example: IF EXISTS ( SELECT 1 FROM my_table LIMIT 1 -- can this improve performance? ) THEN ... END IF;
shmosel
  • 49,289
  • 6
  • 73
  • 138
1
2 3
…
20 21