Questions tagged [sql-limit]
SQL clause to limit number of returned rows
317
questions
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 ...
- 16.3k
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 ...
- 6,623
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?
- 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 ...
- 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
- 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 ?
- 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. ...
- 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?
- 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()...
- 8,260
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
...
- 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 ...
- 867
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 | ...
- 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 '...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
- 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.
- 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`
...
- 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 ...
- 591
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 ...
- 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 ...
- 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 "...
- 479
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 ...
- 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 ...
- 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....
- 665
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 |
+----+--------------+------+---------+------------+
|...
- 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 ...
- 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
- 69
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 ...
- 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.* ...
- 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, ...
- 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'
...
- 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 "...
- 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";
...
user5106392
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;
$...
- 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
...
- 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 ...
- 815
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.*
...
- 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 ...
- 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: '...
- 1,997
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 ...
- 1,884
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 ...
- 43
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 ...
- 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 ...
- 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
)...
- 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 ...
- 1,993
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 ...
- 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 ...
- 2,089