0

I caught a nice and simple bug in my "next data" request with sqlite.

I'm requesting rows with limit 15 and before x date.

The problem/bug is if row 16 (order by date) is the same date as row 15 (the last row in the "request"), the next time I will call my "next request" with limit 15 and before date X (the last row date from the previous request) it will skip row 16.

Now I know I can request before and equal to date and check if I got this row already,

But I wonder if there is magic word in sqlite and maybe it's my lucky day, so I can say to sqlite : "Hey I need the next 15 rows order by date, but don't stop if row 16 (and after him) have the same date" ?

What other are doing in this situation ? I prefer using the date as "cursor" and not rowID incase I will delete and insert rows during app usage.

UPDATE: this is my sql for next request :

""SELECT * from feedItems WHERE object_date < 1600954500 order by object_date DESC limit 15""

What I want : like @forpas said in the comments, I don't want strictly 15 rows if row 16 (and after him) have the same date(tie).

user1105951
  • 2,259
  • 2
  • 34
  • 55

1 Answers1

0

If your version of SQLite support DENSE_RANK, then you can order using that:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY object_date DESC) dr
    FROM feedItems 
    WHERE object_date < 1600954500
)

SELECT *
FROM cte
WHERE dr <= 15;

This approach would retain the 15 most recent dates which happen before some date, possibly returning more than 15 records in the event that the same date occur more than once.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • it's FMDB in macOS app (and I will also use it on iOS). – user1105951 Sep 24 '20 at 14:38
  • Before I do that, you should post the actual query in your question, and ideally also include some sample data. `DENSE_RANK` should probably work here, but maybe my answer needs to change a bit. – Tim Biegeleisen Sep 24 '20 at 14:39
  • It's a very simple sql. I copy it now from running app : "SELECT * from feedItems WHERE object_date < 1600954500 order by object_date DESC limit 15" – user1105951 Sep 24 '20 at 14:42
  • Check the updated answer. Your `ORDER BY` clause implies that you want to retrieve the 15 _most recent_ dates, possibly including more than 15 rows in the event of ties. – Tim Biegeleisen Sep 24 '20 at 14:44
  • Is there a performance issue in your sql I need to consider, compared to my current sql ? – user1105951 Sep 24 '20 at 14:51
  • Yes, of course there is, but then again there is also a performance issue with just using `LIMIT` and `ORDER BY`. Do you really need to select all columns? – Tim Biegeleisen Sep 24 '20 at 14:53
  • No, I mean if you compare my sql to your sql. between them, there is a big difference ? in logic way of speaking "working twice" (my English is little crappy :) )? – user1105951 Sep 24 '20 at 14:55
  • Without checking performance on your database and with your data, I can't say for certain. Also, the query you are asking for cannot be done using `LIMIT` and `ORDER BY`, so what you are asking is a moot point. – Tim Biegeleisen Sep 24 '20 at 14:58
  • Yea. I know there is no "magic" without cost. What I want to understand if the price is very high or acceptable. let's say I have 100,000 records. every time sqlite will run your sql it will be build tmp table of 90,000 row if they object_date < 1600954500 ? with my sql , LIMIT It stop immediately after 15 ? I understand it correctly ? – user1105951 Sep 24 '20 at 15:03
  • 1
    You should run `EXPLAIN` on both queries, and check the execution plans. Yes, my answer probably forces SQLite to store some intermediate result. – Tim Biegeleisen Sep 24 '20 at 15:04
  • I test your sql in my app with dynamic date of the last item date. the problem : each request I get less results. first is 14, then 13, then 12, then 11. I double check it's the same sql syntax. any idea ? – user1105951 Sep 24 '20 at 15:52
  • @user1105951 [Setup a demo here](https://dbfiddle.uk/?rdbms=sqlite_3.27) with your query and test data, and I can take a look. – Tim Biegeleisen Sep 24 '20 at 15:54
  • ok. I think I fixed the problem( was syntax with the dynamic parameter ). Now I got 28,20,18 results. and it "feels" better. But If I understand correctly, If in the "middle" of the 15 rows there are duplicate date, it will fetch them also and count it as -1- in the limit ? – user1105951 Sep 24 '20 at 16:33
  • I test the results and now I see all the duplicated, and got the full picture ! BINGO. thanks you. only thing left for me to check : 1. what's the diff between RANK and DENSE RANK 2. performance. is this heavy thing to run the sql. thanks !! – user1105951 Sep 24 '20 at 16:44
  • 1
    Please read [What's the difference between RANK() and DENSE_RANK() functions in oracle?](https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle). – Tim Biegeleisen Sep 24 '20 at 16:48
  • Now I understand the difference. I just don't understand how this effect my query. in what way ? – user1105951 Sep 24 '20 at 16:52