Questions tagged [offset-fetch]

OFFSET and FETCH is the SQL Standard method of paging, and the standardized way to do LIMIT OFFSET

Filter by
Sorted by
Tagged with
4
votes
1answer
87 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
57 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
15 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
64 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 ...
2
votes
1answer
297 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
152 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
1k 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
287 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
107 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
810 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
131 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
178 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
290 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
176 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) ...