Questions tagged [sql-limit]

SQL clause to limit number of returned rows

Filter by
Sorted by
Tagged with
1340 votes
16 answers
2.4m views

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 ...
Mathieu Longtin's user avatar
465 votes
4 answers
914k views

How to get the top 10 values in postgresql?

I have simple question: I have a postgresql table: 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 ...
Joey Franklin's user avatar
293 votes
9 answers
889k views

How do I do top 1 in Oracle? [duplicate]

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?
Gold's user avatar
  • 61.6k
291 votes
8 answers
614k views

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 ...
Alex's user avatar
  • 67k
168 votes
3 answers
121k views

Sqlite LIMIT / OFFSET query

I have simple question with Sqlite. What is the difference between this: Select * from Animals LIMIT 100 OFFSET 50 and Select * from Animals LIMIT 100,50
Pablo's user avatar
  • 1,681
145 votes
4 answers
200k views

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's user avatar
  • 1,572
133 votes
10 answers
88k views

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. ...
stillinbeta's user avatar
  • 1,987
100 votes
6 answers
175k views

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's user avatar
  • 1,027
94 votes
5 answers
62k views

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()...
EvilPuppetMaster's user avatar
82 votes
1 answer
89k views

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 ...
Lee's user avatar
  • 10.6k
65 votes
4 answers
132k views

Alternatives to LIMIT and OFFSET for paging in Oracle [duplicate]

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 ...
danielpradilla's user avatar
33 votes
1 answer
53k views

SQL (ORACLE): ORDER BY and LIMIT [duplicate]

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 | ...
DraggonZ's user avatar
  • 1,067
32 votes
4 answers
56k views

How to limit returned results of a JOIN query in MySQL [duplicate]

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 '...
Thomas R's user avatar
  • 3,036
27 votes
5 answers
64k views

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 ...
theHack's user avatar
  • 1,954
24 votes
4 answers
6k views

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 ...
shmosel's user avatar
  • 49.9k
23 votes
6 answers
70k views

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 ...
Sandman's user avatar
  • 5,532
18 votes
2 answers
29k views

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 ...
djq's user avatar
  • 15.1k
15 votes
2 answers
23k views

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, ...
Geotob's user avatar
  • 2,875
14 votes
5 answers
40k views

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 <table> thanks.
Skizit's user avatar
  • 44.2k
11 votes
8 answers
34k views

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` ...
Kyle's user avatar
  • 11k
11 votes
2 answers
19k views

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 ...
user1105595's user avatar
11 votes
3 answers
23k views

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 ...
Andrew's user avatar
  • 2,519
10 votes
2 answers
25k views

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 ...
ZioN's user avatar
  • 560
8 votes
4 answers
24k views

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 "...
user1175817's user avatar
8 votes
1 answer
4k views

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 ...
Zev's user avatar
  • 3,471
7 votes
2 answers
4k views

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 ...
CMoney's user avatar
  • 73
7 votes
2 answers
13k views

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....
pravinbhogil's user avatar
7 votes
6 answers
7k views

How to make a dynamic limit in MySQL?

I have a table like this: // notifications +----+--------------+------+---------+------------+ | id | event | seen | id_user | time_stamp | +----+--------------+------+---------+------------+ |...
Martin AJ's user avatar
  • 6,498
7 votes
3 answers
4k views

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 ...
iamawebgeek's user avatar
  • 2,803
6 votes
1 answer
7k views

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's user avatar
6 votes
3 answers
2k views

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 ...
ely's user avatar
  • 76.1k
5 votes
3 answers
3k views

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.* ...
groseb's user avatar
  • 59
5 votes
4 answers
16k views

Limiting a SQLite query in android [closed]

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, ...
Marko Cakic's user avatar
  • 7,016
5 votes
3 answers
7k views

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' ...
kolypto's user avatar
  • 33.5k
5 votes
3 answers
3k views

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 "...
wutzebaer's user avatar
  • 14.6k
4 votes
2 answers
7k views

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"; ...
user avatar
4 votes
1 answer
3k views

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; $...
Ben's user avatar
  • 5,727
4 votes
1 answer
3k views

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 ...
Levi H's user avatar
  • 3,496
4 votes
2 answers
671 views

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 ...
Dongminator's user avatar
4 votes
2 answers
132 views

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.* ...
BenMorel's user avatar
  • 35.6k
4 votes
2 answers
856 views

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 ...
lickmycode's user avatar
  • 2,079
4 votes
0 answers
1k views

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: '...
Aren Hovsepyan's user avatar
3 votes
4 answers
1k views

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 ...
Nikša Baldun's user avatar
3 votes
4 answers
7k views

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

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 ...
user6347191's user avatar
3 votes
3 answers
18k views

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 ...
Hailwood's user avatar
  • 91k
3 votes
1 answer
5k views

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 ...
Eugene Brown's user avatar
  • 4,192
3 votes
3 answers
458 views

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 )...
nickbusted's user avatar
  • 1,059
3 votes
1 answer
842 views

Why does adding ORDER BY drastically speed up query?

I've discovered some very strange and counter-intuitive behaviour in PostgreSQL. I have a query structure as follows. I am selecting both the IDs and the count from a subquery. The subquery does the ...
robbieperry22's user avatar
3 votes
1 answer
1k views

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 ...
Ben Brocka's user avatar
  • 2,074
3 votes
1 answer
4k views

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 ...
Ryder Brooks's user avatar
  • 2,089

1
2 3 4 5
7