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
6
votes
2 answers

Limit number of rows per group from join (NOT to 1 row)

Given these tables: TABLE Stores ( store_id INT, store_name VARCHAR, etc ); TABLE Employees ( employee_id INT, store_id INT, employee_name VARCHAR, currently_employed BOOLEAN, etc ); I want to list the 15 longest-employed employees for…
CMoney
  • 63
  • 1
  • 6
5
votes
3 answers

Strange results using order by and limit

I'm trying to set up a pagination using SQL. I want 3 results per page and here is what I have done : SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 0; --Page 1 SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC LIMIT 3 OFFSET 3;…
groseb
  • 59
  • 1
  • 6
5
votes
3 answers

UPDATE .. LIMIT 1 with SqlAlchemy and PostgreSQL

With SqlAlchemy, is it possible to build a query which will update only the first matching row? In my case, I need to update the most recent log entry: class Log(Base): __tablename__ = 'logs' id = Column(Integer, primary_key=True) #... …
kolypto
  • 31,774
  • 17
  • 105
  • 99
5
votes
4 answers

Limiting a SQLite query in android

I am using an SQLite database in my android application, and I have a function which selects the rows from a certain table: public Cursor getAllDiscounts() { // return db.query(table, columns, selection, selectionArgs, groupBy, // having,…
Marko Cakic
  • 6,936
  • 9
  • 27
  • 34
5
votes
3 answers

Postgres min function performance

I need the lowest value for runnerId. This query: SELECT "runnerId" FROM betlog WHERE "marketId" = '107416794' ; takes 80 ms (1968 result rows). This: SELECT min("runnerId") FROM betlog WHERE "marketId" = '107416794' ; takes 1600 ms. Is there a…
wutzebaer
  • 14,365
  • 19
  • 99
  • 170
4
votes
1 answer

How to use LIMIT and OFFSET when joining one to many tables with postgresql?

I am using sqlalchemy core with a postgres database, and am implementing the JSON:API spec. For pagination I am just using basic LIMIT and OFFSET. So for example with the following two tables: session id name appearance id date session_id For…
Levi H
  • 3,426
  • 7
  • 30
  • 43
4
votes
2 answers

Convert SET ROWCOUNT n to TOP (n) when n could be 0

We are currently using SQL server 2016. Based on the Microsoft page Deprecated Database Engine Features in SQL Server 2016, the SET ROWCOUNT is being deprecated. So we are trying to convert all SET ROWCOUNT N statements to TOP(N) statements. It…
Dongminator
  • 795
  • 10
  • 15
4
votes
0 answers

Sequelize hasOne include not working properly

I am trying to include hasOne association with findAll method like below await db.Symbol.findAll({ where:{ symbol: symbols }, attributes: ['symbol'], include: [{ as: 'price', model: db.Price, …
Aren Hovsepyan
  • 1,947
  • 2
  • 17
  • 45
4
votes
2 answers

LIMITing a SQL JOIN, with JOIN conditions

I have a problem similar to LIMITing a SQL JOIN, but with a slightly more complex requirement. I want to search for Users and associated Transactions, which lie within a time range: SELECT u.*, t.* FROM User u JOIN Transaction t ON t.user_id =…
BenMorel
  • 34,448
  • 50
  • 182
  • 322
4
votes
2 answers

Simple MySQL query is blocking when adding ORDER BY with LIMIT

I have a table tb_profilecomments: 4,3 GiB -- 8 Million rows total -- InnoDB with 3 indexes: The query I run is simple: SELECT * FROM tb_profilecomments WHERE profilecomment_user_id=6430 ORDER BY profilecomment_id DESC Getting a result in less…
lickmycode
  • 2,069
  • 2
  • 19
  • 20
4
votes
2 answers

MySQL syntax error using LIMIT command with Prepared Statement in Java

I am writing code in Java and I want to take every time I run this code the next line from a MySQL table.The second time I run this code is this. String timh1 = "1"; String timh2 = "2"; PreparedStatement st = null; String sqlGrammes = "SELECT…
user5106392
4
votes
1 answer

Laravel using skip and take limiting query

I'm trying to build a query that uses skip() and take() functions, for some reason it keeps adding offset 0 at the end instead it should be LIMIT 0,0 Code: $dbSearch = new SchoolInfo; $dbSearch = $dbSearch->where(function($query)…
Ben
  • 5,627
  • 9
  • 35
  • 49
3
votes
1 answer

CakePHP: Limit Fields associated with a model

I have several fields in some of my database tables that my CakePHP models never need to retrieve. Is there some way to set a default set of fields to fetch at the model level? For instance I retrieve some data from a third party designed database…
Ben Brocka
  • 2,006
  • 4
  • 34
  • 53
3
votes
1 answer

DESCRIBE with LIMIT in spark sql

I am using the DESCRIBE keyword to get column information about a temp view. It's a useful method however I have a table for which I'd only like to describe a subset of the columns. I'm trying to use LIMIT in conjunction with DESCRIBE to achieve…
Eugene Brown
  • 4,032
  • 6
  • 33
  • 47
3
votes
2 answers

How to use limit and order by in a delete query in Zend2

I have following sql query and I need to write it in zend2. DELETE FROM test_table WHERE user_id = 2 ORDER BY id ASC LIMIT 10 I tried with this: $sql = new Sql($this->dbAdapter); $delete =…
Andreea
  • 151
  • 1
  • 3
  • 12
1 2
3
…
20 21