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
3
votes
4 answers

How to return 1 row for each distinct value in a given column in Oracle?

I have data that looks like NBR ID DT 1 1 01-DEC-01 1 2 01-JAN-01 2 3 01-JAN-01 2 4 O2-JAN-01 I want to get just one row for each NBR, with the earliest date. So I want the result to be NBR ID DT 1 2 01-JAN-01 2 3 …
user6347191
  • 43
  • 1
  • 5
3
votes
1 answer

Getting incorrect number of rows when using LIMIT in PHPMyAdmin

I recently installed XAMPP to do some web development practice and am having issues with the following query in PHPMyAdmin. When I run the following query: SELECT f.name FROM forums f INNER JOIN messages m USING (forum_id) ORDER BY…
Joe Devine
  • 161
  • 11
3
votes
4 answers

Select first record if none match

In PostgreSQL, I would like to select a row based on some criteria, but if no row matches the criteria, I would like to return the first row. The table actually contains an ordinal column, so the task should be easier (the first row is the one with…
Nikša Baldun
  • 1,854
  • 4
  • 28
  • 39
3
votes
3 answers

Minimum number of rows per group

The following is my query: SELECT * FROM ( SELECT f.max, f.min, p.user_id, p.id, p.title, p.rating, RANK() OVER ( PARTITION BY p.user_id ORDER BY p.rating DESC, p.id DESC ) AS rnk FROM posts AS p INNER JOIN…
nickbusted
  • 1,029
  • 4
  • 18
  • 30
3
votes
1 answer

PostgreSQL window function with LIMIT

The query below groups the results from first into 4 equally spaced date bins and aggregates an average for the_value in each bin. WITH first as( SELECT extract(EPOCH FROM foo.t_date) as the_date, foo_val as the_value FROM bar INNER JOIN…
Ryder Brooks
  • 2,049
  • 2
  • 21
  • 29
3
votes
2 answers

Does SQLite guarantee the order of results to match a table?

If table Scores looks like this: _id | score --------- 1 | 1,000 2 | 2,000 3 | 3,000 4 | 4,000 5 | -1 6 | -1 7 | -1 Will the following query always return the rows in _id ascending order? SELECT * FROM Scores Also, will the…
user1908746
3
votes
1 answer

What is the exact behaviour of an INSERT IGNORE.... SELECT.... ORDER BY... LIMIT... in MySql?

In MySql 5.6 I have a query similar to the following: INSERT IGNORE INTO TABLE_1 (field_a,field_b) SELECT field_a,field_b FROM TABLE_2 WHERE ... ORDER BY field_a LIMIT 0,10 TABLE_1 is a temporary table that is used to store some tuples and then…
clami219
  • 2,958
  • 1
  • 31
  • 45
3
votes
0 answers

With MySQL Can the Query LIMIT use an optional value in a PreparedStatement?

Specifically, in my prepared statement, can I do something akin to: ... LIMIT IFNULL(?,1000) meaning, use the limit passed in as a parameter, but if passed as null use 1000? So far, my experimenting seems to indicate that a function cannot be used…
Lawrence Dol
  • 63,018
  • 25
  • 139
  • 189
3
votes
3 answers

CodeIgniter active records offset without limit?

I have two variables, $take (limit) and $skip (offset) which are the values for a limit clause in mysql. Now Code Igniter does it's limit clauses backwards. E.g. $this->db->limit(5,10) would product LIMIT 10, 5 in MYSQL. Anyway, I am having a hard…
Hailwood
  • 89,623
  • 107
  • 270
  • 423
2
votes
2 answers

How to automatically limit the number of rows in a database?

I have the following table +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ |…
Riki Lyng
  • 317
  • 4
  • 19
2
votes
0 answers

How does LIMIT accept a parameter

Using DBFiddle, I create a table create table t(x int); insert into t values(1),(2),(3); LIMIT works select * from t limit 1,1; returns x|2 It is documented that user-defined variables are not allowed. User variables may be used in most contexts…
JMP
  • 4,417
  • 17
  • 30
  • 41
2
votes
1 answer

How to limit the result by categories on the query

The following code shows me the posts on the carousel ordered by date and with exceptions for some categories. But if someone posts in the same category it takes the full list. What I am trying to do is limit in two posts per category. I would…
Adriano
  • 31
  • 2
2
votes
1 answer

SELECT FOR UPDATE subquery not respecting LIMIT clause under load

I have the following query that uses the psql SKIP LOCKED feature to poll messages from a queue table sorted by the insertion time. My subquery also limits the result set by 10 since that is the maximum number of results that can be processed by the…
Dan
  • 1,805
  • 2
  • 18
  • 21
2
votes
1 answer

Limit and offset with a window function

Is there any chance that I use a window function to use a limit + offset parameters in order to have a result as a page ? i.e I want to limit/offset by depname to show only 2 department, but all the rows where this department appears : depname |…
BabC
  • 1,044
  • 5
  • 18
2
votes
1 answer

Postgresql update 200 random records

I've got a query returning 200 random distinct foo from my Database. I want to update the notes field for those rows. select distinct on (foo, random()) * from test order by random(), foo limit 200 update test notes = 'Flag' order by random() limit…
ClaskoTheKnight
  • 55
  • 7
1 2 3
…
20 21