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
22
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
  • 5,432
  • 5
  • 20
  • 23
18
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
  • 14,810
  • 45
  • 122
  • 157
15
votes
2 answers

Performance of max() vs ORDER BY DESC + LIMIT 1

I was troubleshooting a few slow SQL queries today and don't quite understand the performance difference below: When trying to extract the max(timestamp) from a data table based on some condition, using MAX() is slower than ORDER BY timestamp LIMIT…
Geotob
  • 2,847
  • 1
  • 16
  • 26
12
votes
5 answers

How to limit the results on a SQL query

I'm wondering is it possible to limit the result of a SQL request? For example, only return up to 50 rows from: SELECT * FROM thanks.
Skizit
  • 43,506
  • 91
  • 209
  • 269
11
votes
3 answers

PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?

I'm looking for something similar this in SQL Server: SELECT TOP n WITH TIES FROM tablename I know about LIMIT in PostgreSQL, but does the equivalent of the above exist? I'm just curious as it would save an extra query each time for me. If I have a…
Andrew
  • 2,519
  • 6
  • 29
  • 46
11
votes
8 answers

passing LIMIT as parameters to MySQL sproc

I'm creating a paging class and need to pass in two parameters to my MySQL stored procedure for the LIMIT clause. I'm passing them in as INTs and trying something like this SELECT * FROM `MyTable` LIMIT MyFirstParamInt, MySecondParamInt it gives me…
Kyle
  • 10,839
  • 17
  • 53
  • 63
11
votes
2 answers

Select elements where sum of field is less than N

Given this table: # select * from messages; id verbosity 1 20 2 20 3 20 4 30 5 100 (5 rows) I would like to select N messages for which sum of verbosity is lower than N. So if N = 70, the desired result will be messages with id…
user1105595
  • 591
  • 2
  • 8
  • 20
10
votes
2 answers

DB2 Using LIMIT and OFFSET

I am developing a Java Web service allow paging when fetching big data set from a DB2 Database on a IBM Mid Range Machine (AS400). For example; if there are 10000 records in a data set, I want to fetch them in 1000 blocks at a time. I found this…
ZioN
  • 550
  • 2
  • 11
  • 35
7
votes
1 answer

Slow Postgres query using LIMIT

I'm experiencing an issue similar to PostgreSQL query very slow with limit 1 and Extremely slow PostgreSQL query with ORDER and LIMIT clauses although in my case it doesn't matter if the LIMIT is 1, 5 or 500. Basically, when I run a query generated…
Zev
  • 3,423
  • 1
  • 20
  • 41
7
votes
2 answers

Postgres query result to json object

My query is given below SELECT w.payload, Count('payload') OVER () AS ROWCOUNT FROM wholesale_confirmation.wholesale_order_confirmation w WHERE w.delivery_date = COALESCE(NULL, w.delivery_date) AND w.ship_to_location_id = COALESCE(NULL,…
pravinbhogil
  • 657
  • 1
  • 9
  • 13
7
votes
6 answers

How to make a dynamic limit in MySQL?

I have a table like this: // notifications +----+--------------+------+---------+------------+ | id | event | seen | id_user | time_stamp | +----+--------------+------+---------+------------+ | 1 | vote | 1 | 123 | 1464174617…
Martin AJ
  • 6,261
  • 8
  • 53
  • 111
7
votes
3 answers

Get n grouped categories and sum others into one

I have a table with the following structure: Contents ( id name desc tdate categoryid ... ) I need to do some statistics with the data in this table. For example I want to get number of rows with the same category by grouping and id of…
iamawebgeek
  • 2,713
  • 1
  • 18
  • 34
7
votes
4 answers

Limit join to one row

I have the following query: SELECT sum((select count(*) as itemCount) * "SalesOrderItems"."price") as amount, 'rma' as "creditType", "Clients"."company" as "client", "Clients".id as "ClientId", "Rmas".* FROM "Rmas" JOIN "EsnsRmas"…
user1175817
  • 449
  • 2
  • 7
  • 17
6
votes
1 answer

How to set LIMIT within a conditional statement; POSTGRESQL

I want to order the result by id in descending order and then LIMIT the number of rows obtained based on a @condition ORDER BY id DESC IF @condition is TRUE THEN LIMIT 1 ELSE nothing END IF
Himank Jog
  • 69
  • 1
  • 6
6
votes
3 answers

How to guarantee that at least N rows are returned by recursive CTE in Postgres

Most resources that describe a SELECT TOP ... query in Postgres say that you should use LIMIT instead, possibly with an ORDER BY clause if you need to select the top elements by some ordering. What do you do if you need to select the top N elements…
ely
  • 74,674
  • 34
  • 147
  • 228
1
2
3
…
20 21