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.

258 questions
1123
votes
14 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
  • 13,574
  • 6
  • 24
  • 34
314
votes
4 answers

How to get the top 10 values in postgresql?

I have simple question: I have a postgresql database: 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
  • 4,883
  • 5
  • 22
  • 21
272
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
  • 60,472
  • 154
  • 401
  • 592
140
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,522
  • 2
  • 10
  • 11
117
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,797
  • 3
  • 15
  • 22
68
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
  • 7,052
  • 9
  • 31
  • 30
65
votes
5 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
  • 677
  • 1
  • 5
  • 4
55
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
  • 757
  • 1
  • 7
  • 15
55
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
  • 9,876
  • 3
  • 32
  • 43
31
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,027
  • 1
  • 15
  • 22
29
votes
4 answers

LIMITing an SQL JOIN

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
  • 2,986
  • 5
  • 29
  • 31
24
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,690
  • 9
  • 22
  • 32
19
votes
6 answers

Retrieving only a fixed number of rows in MySQL

I am testing my database design under load and I need to retrieve only a fixed number of rows (5000) I can specify a LIMIT to achieve this, however it seems that the query builds the result set of all rows that match and then returns only the…
Sandman
  • 4,432
  • 4
  • 18
  • 23
15
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
  • 42,915
  • 5
  • 56
  • 120
14
votes
2 answers

Update top N values using PostgreSQL

I want to update the top 10 values of a column in table. I have three columns; id, account and accountrank. To get the top 10 values I can use the following: SELECT * FROM accountrecords ORDER BY account DESC LIMIT 10; What I would like to do…
djq
  • 13,348
  • 42
  • 111
  • 148
1
2 3
…
17 18