Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
1 vote
2 answers
175 views

Why OFFSET / FETCH causing Performance issues with Queries in MS SQL 2019?

I have this select query: SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME, ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE, ...
Subin Benny's user avatar
2 votes
1 answer
908 views

What's the difference between FETCH FIRST and FETCH NEXT in PostgreSQL?

What's the difference between FETCH FIRST and FETCH NEXT in PostgreSQL? The docs simply say NEXT Fetch the next row FIRST Fetch the first row of the query which isn't much of an explanation. I ...
Sergey Zolotarev's user avatar
0 votes
1 answer
159 views

'Limit INT OFFSET @variable' throw runtime error in mysql server

I'm trying to solve this problem in Hackerrand The problem asks for the median in a specific column my code: SET @VAE = (SELECT COUNT(*) FROM STATION); SET @VAE1 = ROUND(@VAE/2)-1; SELECT LAT_N FROM ...
ahmad alghadban's user avatar
0 votes
1 answer
538 views

Why does my LIMITed SELECT fetch the last record of the table, not the first?

I read that to fetch the first table record, you may use LIMIT 1. I tried to apply that knowledge to my JOIN statement and got a diametrically opposite result. It joined the last record of the joined ...
Sergey Zolotarev's user avatar
1 vote
0 answers
1k views

Best way to implement Pagination in SQL Server

I have a complex stored procedure that takes a customerId and some other parameters and returns thousands of rows. We want to implement pagination in this SP since the number of rows being returned is ...
lifeisajourney's user avatar
1 vote
0 answers
711 views

limit user's SELECT query to a fixed number of rows

i have read the following questions: Limit Postgres results for a user and also noted the recommendation of limiting a user's statement_timeout, however i would like something a little more consistent....
gagan's user avatar
  • 11
3 votes
2 answers
2k views

Why doesn't an OFFSET query benefit more from the index?

I've got a simple example books table with an integer id primary key ("books_pkey" PRIMARY KEY, btree (id)) and 100,000,000 random rows. If I run: EXPLAIN SELECT * FROM books ORDER BY id ...
Nathan Long's user avatar
1 vote
1 answer
758 views

LIMIT and OFFSET a complex query

I have a query generated by a ORM (Sequelize), i ran into an issue where sequelize fails, look at those issues. https://github.com/sequelize/sequelize/issues/7344 https://github.com/sequelize/...
Raman Shekhawat's user avatar
0 votes
0 answers
17 views

Analysis of Conditions with no results and high offset number - PostgreSQL JSONB [duplicate]

I am asked to record down the analysis on PostgreSQL JSONB query having Conditions with high offset number and query that provide no results considering the below. select * from sub_test df, ...
Lingamoorthy Bheeman Balan's user avatar
1 vote
1 answer
824 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 ...
Alex's user avatar
  • 113
4 votes
1 answer
9k 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 ...
Veksi's user avatar
  • 269
4 votes
1 answer
1k 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/...
igelr's user avatar
  • 2,132
4 votes
1 answer
2k 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;
James Hiew's user avatar
2 votes
1 answer
3k 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 ...
Slartibartfast's user avatar
5 votes
1 answer
11k 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'...
Saroj Shrestha's user avatar
4 votes
1 answer
1k 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 ...
Evan Carroll's user avatar
  • 63.5k
1 vote
2 answers
6k 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 ...
saher mohamed's user avatar
4 votes
1 answer
5k 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 * ...
Svn342's user avatar
  • 41
9 votes
3 answers
9k 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 ...
Evan Carroll's user avatar
  • 63.5k
1 vote
0 answers
788 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 ...
user41527's user avatar
  • 111
4 votes
2 answers
193 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 ...
Khalil's user avatar
  • 143
3 votes
2 answers
2k 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 ...
Steve Buchok's user avatar
1 vote
0 answers
186 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] < ...
Fred's user avatar
  • 121
1 vote
1 answer
3k 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 ...
Matt Healy's user avatar
  • 1,342
5 votes
1 answer
644 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 ...
blockhead's user avatar
  • 163
5 votes
2 answers
8k views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
user606521's user avatar
  • 1,365
3 votes
1 answer
1k 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 ...
Ace Subido's user avatar
15 votes
3 answers
4k 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 ...
usr's user avatar
  • 7,340
0 votes
2 answers
3k 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) ...
Praveen Prasannan's user avatar