My app shows the 10 latest records, and works similarly to email clients:

• it shows the latest 10 records
• at the bottom of the list there's a "load more" button; it loads the next (i.e. older) 10 records

In pseudocode this is what my client asks the server:

• order by reverse PK (or date)
• skip 10
• take 10

But that doesn't work. Let's say the user is also using the system via the app on his phone, and he adds 2 records from there. So the desktop app believes it has the latest 10 records, and that the table on the server looks like this:

1 2 3...31 32 33 34 35 36 37 38 39 40            # app has 31..40

But the table actually looks like this:

1 2 3...31 32 33 34 35 36 37 38 39 40 41 42

So if the client skips/takes 10, it won't get 21..30 as expected, but rather 23..32, thus:

• it won't get the latest 2 records 41 42
• it will get 2 duplicate records 31 32

Ideally, the client should fetch these records: 23..30 AND 41..42. I could do that with multiple queries, but I want to avoid that, if possible.

Without getting bogged down in SQL (because I'd need to translate it for my ORM (EF) anyway), I'm interested in the underlying logic to solving this problem. Put differently, I'm not struggling with the SQL, but rather with how to think about this problem in principle, in pseudocode. How do I approach this?

CodePudding user response：

You have read IDs 31 to 40, and you remember these IDs. Then IDs 41 and 42 got added, and you want to select IDs 42, 41, 30 .. 23. These are the highest IDs found outside the selected range. So, just select everything outside the already selected range, order by ID in descending order and fetch 10 rows.

You don't want the SQL. Here it is anyway :-)

select *
from mytable
where id > :id_newest or id < :id_oldest
order by id desc
fetch first 10 rows only;

CodePudding user response：

Here's a way to do it with two queries:

• client determines its
  • oldest PK = id_oldest
  • newest PK = id_newest
• issue query 1 to server, to get newest records:
  • order by PK
  • where id > id_newest
  • take 10
• issue query 2 to server, to get "next oldest" page:
  • order by reverse PK
  • where id < id_oldest
  • take 10 - count(query1)
• the two queries would return a maximum of 10 records in total

The client must then add the newest to the top of the list, add the oldest to the bottom.

(If there are many new records on the server, not all would be fetched; so another request, or more, may be needed.)

I'm not marking this as the solution, as it's hacky. I hope there's a better way with one query.