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
0
votes
1 answer

Random row selection using MySQL returns NULL

I am trying to get a random row from MySQL table but all three attemps: $query = "SELECT cid FROM table LIMIT 1 OFFSET ".rand(1,$num_rows); $query = "SELECT cid FROM table OFFSET RANDOM() * (SELECT COUNT(*) FROM table) LIMIT 1"; $query = "SELECT *…
Jason Stallard
  • 349
  • 2
  • 15
0
votes
1 answer

How to limit result set with multiple RETURN QUERY in PL/pgSQL

I have function with multiple RETURN QUERY statements. At the end I want to get only N random records. If I set LIMIT max_size to each RETURN QUERY statement at the end I'll get max_size * count of RETURN QUERY times. Short version of my…
azhidkov
  • 1,047
  • 3
  • 14
  • 23
0
votes
3 answers

Limit results of all MySQL queries

I wrote a PHP/MySQLi frontend, in which the user can enter SQL queries, and the server then returns the results in a table (or prints OK on INSERTs and UPDATEs) As printing the results can take a very long time (e.g. SELECT * FROM movies) in a IMDb…
s3lph
  • 4,575
  • 4
  • 21
  • 38
0
votes
2 answers

PHP & MySQL: Delete all old rows keeping 25 of the newest rows

Let's make this simple: I have a table called chat that has many rows in, every user can add a new message and this means the table can get quite large. (I have looked online and it's not very 'clear' on some examples.) I need to delete all older…
DannySMc
  • 27
  • 8
0
votes
1 answer

MySQL LIMIT doesn't work / scope

I've got that rank query with left join. Trying to limit results to 3 rows, but getting all results instead. SELECT CONCAT(users.name, users.surname) n, total_time v, 1+(SELECT count(*) from app a WHERE a.total_time > b.total_time) r…
axldns
  • 55
  • 7
0
votes
1 answer

Setting a LIMIT (SQL) when accessing a property of an ORM object

I started reviewing the performed SQL queries so I can improve them (caching, reformulating them, etc) in my Django project. For example, to get a single object from the database, this: Company.objects.filter(pk=1)[:1] Is faster…
user125498
  • 25
  • 4
0
votes
3 answers

SELECT ... LIMIT 0,1 syntax error

for($nr = 0; $nr < 2; $nr++){ print $nr; print(gettype($nr)); // prints 0integer $result = mysqli_query($con,"SELECT * FROM phcdl_files ORDER BY file_id DESC LIMIT '$nr',1") or die(mysqli_error($con)); …
Pedro
  • 416
  • 1
  • 8
  • 24
0
votes
0 answers

MySQL alternate to group_concat?

Using group_concat apparently has a size limit on it.... so I'm looking for another way to achieve this. Database structure: John Doe CA Col1 Col2 Col3 John Doe CA Col1 Col2 Col3 John Doe CA Col1 Col2 Col3 Jane Doe CA Col1 Col2 …
whispers
  • 962
  • 1
  • 22
  • 48
0
votes
1 answer

Syntax error in MySQL LIMIT clause

I'm having a problem with a query: $sth = $Db->dbh->prepare( "SELECT * FROM loader WHERE download = 0 AND lastconnected BETWEEN DATE_SUB(NOW(),INTERVAL 15 MINUTE) AND NOW() ORDER BY lastconnected DESC LIMIT :amount"); The…
user2879055
  • 176
  • 9
0
votes
1 answer

How to use ORDER BY and LIMIT with UPDATE?

Its straight forward. I have no multiple tables. How can I get the following SQL to work? "UPDATE table SET (...) WHERE (...) LIMIT 2 ORDER BY something"
Sarchophagi
  • 377
  • 2
  • 5
  • 20
0
votes
1 answer

Can I use a parameter for the LIMIT condition in sqlite query

Quick question on an SQLite query I'm working on for a C# application: Can I use a parameter to set the "LIMIT" value in a query? For example I would like to do this: SQLiteCommand cmd = new SQLiteCommand("SELECT FROM ... WHERE ... LIMIT…
gnarlybracket
  • 1,691
  • 4
  • 18
  • 37
0
votes
1 answer

Select from row to last row

Is it possible for me with MySQL to select the from a specific row to the end of the table, without knowing how many rows there are left? My query at the moment is: SELECT * FROM updates WHERE userid='$fid' ORDER BY up_id DESC But I want to be able…
Colum
  • 996
  • 2
  • 8
  • 23
0
votes
1 answer

Is it possible to do ORDER BY RAND() LIMIT but include the header?

Is it possible to return a random selection of rows from user table and include the header as the first row? The SQL I have at the moment just returns 5 random rows (not necessarily the header) SELECT 'id', 'name' UNION ALL SELECT id, …
xylar
  • 7,433
  • 17
  • 55
  • 100
0
votes
2 answers

Ensure that the correct number of rows are returned from a MySQL query

I have a (game) leaderboard table which contains a rank which is updated by another query. It is centered around the ID - when I query the table I ideally want to get back N records. So I have done a UNION on the records both with the limit N/2 like…
Danny Birch
  • 603
  • 4
  • 16
0
votes
1 answer

correct syntax for limit union and order by clause with local variables and randomization in mysql : Error(1221)

I am trying to create a random sample (30-70) as below -: set @total=(select count(*) from tab_1 where predict_var ="4" or predict_var ="2" ) ; set @sample= (select @total*(70/30)) ; #@total,@sample #4090,9543.333331970 # Post this I can fetch…
ekta
  • 1,560
  • 3
  • 28
  • 57
1 2 3
…
20 21