Questions tagged [offset-fetch]
OFFSET and FETCH is the SQL Standard method of paging, and the standardized way to do LIMIT OFFSET
20
questions
0
votes
1answer
32 views
Distinct result with row_id for limit query
There are 2 tables:
CREATE TABLE sample1 (
id int,
name varchar,
score int,
mode int
)
CREATE TABLE sample2 (
id int,
mode int
...
)
I need to select distinct name with MIN(score) and ...
0
votes
0answers
39 views
Order By not working correctly in common table expression in Fetch clause while retrieving data
I have been working on a stored procedure optimization which fetches data from multiple tables with many left joins.
The problem I am facing is that in common table expression while fetching the ...
4
votes
1answer
719 views
How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?
It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.
In the following, I have a query ...
3
votes
1answer
193 views
How to get tsql TOP PERCENT with OFFSET
select top 10 percent *
from sys.databases
order by database_id
I want to get the same 10 percent result using offset but this query works wrong
offset 0 rows
fetch next (select cast((count(*) * 10/...
1
vote
0answers
57 views
Difference between LIMIT N and FETCH NEXT N ROWS?
The two queries below seem equivalent. Is one better than the other? e.g. in terms of performance.
select * from some_table offset 10 limit 10;
select * from some_table offset 10 fetch next 10 rows;
2
votes
1answer
213 views
Get total number of rows when offset is greater than max
I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function:
select count(*) over() as count, name, id from users
And since the consumer app ...
3
votes
1answer
1k views
Reduce query time for higher offset in sql server
Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows:
CREATE Procedure [dbo].[spTestingBaseVoter]
@SortColumn NVARCHAR(128) = N'...
2
votes
1answer
362 views
What is the SQL Standard method of doing LIMIT OFFSET?
I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up,
The ...
1
vote
2answers
2k views
How to get the top 20 percent records with a single query?
SELECT start_coins
FROM coins
ORDER BY start_coins DESC LIMIT 10; /* till here the query works*/
CAST((COUNT(start_coins) * 0.2) AS INT);
I want the number returning from the count to be used as ...
3
votes
1answer
2k views
MSSQL efficient paging including a join on big tables
We have a table with products and a table with a current selection/assortment of products. Now need to join them create an export file by some .net code.
Currently using query like this:
SELECT * ...
8
votes
3answers
2k views
SQL Server Error, “Invalid usage of the option FIRST in the FETCH statement.”
From 2012 onward the SQL Server docs show they support OFFSET..FETCH which I'm trying to use instead of a LIMIT.
The following works fine in PostgreSQL to sample a result set,
SELECT *
FROM ( VALUES ...
1
vote
0answers
356 views
MySQL: Limit number of rows returned with a given column value
My table has a list of activities, each associated with a customer and each with a date when the activity happened. I want to group by the customer ID and the month and the year, then sort by the ...
4
votes
2answers
111 views
Implicit conversion in fetch
While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint.
Example table and query:
CREATE TABLE checkPagintion
(
Id INT NOT NULL ...
2
votes
2answers
944 views
OFFSET X ROWS FETCH NEXT 50 ROWS ONLY not using indexes
I have a query that is using OFFSET X ROWS FETCH NEXT 50 ROWS ONLY. If X is 160745 it uses the index, if X is 160746 it does not. There are more than 400000 records. With the index, it takes about 2 ...
1
vote
0answers
140 views
Logical read when use Offset in SQL
When I execute this script
SET STATISTICS IO ON
SELECT
*,
ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO]
FROM
vwTblTest
WHERE
([Factor] >= 139501 AND [Factor] < ...
0
votes
1answer
344 views
Speed up ORDER BY and LIMIT in MySQL
I have a table with contact records, and clients wish to search this table to find records with a matching email address (filtered by account ID). The results are paginated by a RESTful API.
This ...
5
votes
1answer
316 views
OFFSET … FETCH overlapping results on second page
When using OFFSET ... FETCH, I'm having I'm getting...interesting results.
Here are two sqlfiddles to illustrate my problem.
http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8
...
2
votes
1answer
208 views
Slow Query with LIMIT
We have this query:
SELECT "messages".*
FROM "messages"
WHERE "messages"."account_id" = 1
AND "messages"."status" = 'unread'
AND (created_at >= '2014-04-24 01:00:00.000000')
ORDER BY id DESC
It ...
15
votes
3answers
3k views
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
0
votes
2answers
2k views
limit and then group by in MySQL
I have a mysql table having following structure :
payment_id(int auto_increment),
emp_id(int),
chargeTime(datetime),
payment(double)
I need to have three latest (with respect to chargeTime) ...
