473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Issue with using limit with offset

I am using Postgres 9.6.9.

I am facing a strange issue with using limit with offset.

Without any limit and offset conditions, I get 9 records.

If I give conditions like-

OFFSET 1 LIMIT 3

OFFSET 2 LIMIT 3

I get the expected no (3) of records at the desired offset.

However I only get 2 records for the following-

OFFSET 5 LIMIT 3

OFFSET 6 LIMIT 3

I should get 3 , as the total no. of records is 9.

For the following conditions I get 3 records-

OFFSET 5 LIMIT 4

OFFSET 6 LIMIT 4

It seems that under some conditions, the LIMIT is internally being decreased by 1 when used with OFFSET.

This is baffling, thanks in advance for any help.
Apr 16 '19 #1
1 1681
Rabbit
12,516 Expert Mod 8TB
Are you using an order by?

According to the documentation from PostgreSQL:
When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.
Apr 16 '19 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: mimisam | last post by:
I have a table with a few hundreds records of products information. I need to retrieve the top 10 records based on the sum of the products quantity. My query is as below: Select *, sum(qty) as...
24
by: Christopher Benson-Manica | last post by:
Is there anything wrong with my attempt (below) at implementing something resembling a smart pointer? template < class T > class SmartPointer { private: T *t; public:
4
by: Gactimus | last post by:
Here is a program that encodes and decodes a text file. What I need to do is write a C++ program that requests 3 different file names. One filename is for the source file to be encoded, another is...
0
by: D. Dante Lorenso | last post by:
I need to know that original number of rows that WOULD have been returned by a SELECT statement if the LIMIT / OFFSET where not present in the statement. Is there a way to get this data from PG ?...
0
by: qh0st | last post by:
Hi all. I have a frustrating issue that I can't seem to figure out. EXPERTS please help me~! Deadline is coming up soon~! My task is to localize a winform application. The .Net Designer...
6
by: Chris Johnson | last post by:
Greetings all. I am really stuck on this one as I can't seem to grok if I am abusing the C++ language or if I am simply using components of the C++ Standard Library incorrectly. Here is the code:...
0
by: nhaughton | last post by:
I have writtten a webservice in .Net 1.1 using C#, that exposes an existing COM+ application as a webservice to remote web applications. The COM+ application works fine under Win 2000, XP amd...
0
by: David | last post by:
Hi, I have an asp page which I want to display 10 records with a 'Previous' and 'Next' link underneath for navigating through the rest of the recordset. I have the page displaying the first...
11
donilourdu
by: donilourdu | last post by:
hi, I am trying to retrive data from MYSQL database.I am using limit to fetch the data.It will fetch two rows instead of fetching ten rows.But when i try to debug it fetches eight rows instead...
9
by: mikee99 | last post by:
We are using the backup program Microlite Backup Edge on a Centos OS. When running the program from the menu, we can backup with no problem. When we schedule a backup in cron, we get an error "file...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.