SQLite Forum

dense ranking

dense ranking

(1.1) By Gert Van Assche (GertVA) on 2021-06-21 21:28:19 edited from 1.0 [source]

Hi all, 

I have a table with paragraphs and sentences in those paragraphs. 

I need to split this in 2 setsso I'm using:

SELECT dense_rank() over (order by [paragraph] ASC) as ranknum, row_number() over (partition by [paragraph]) as rankcounter, ...

And then: 
* the first 5 lines -- easy: ... WHERE rankcounter < 6
* the last 5 lines  -- and that I don't know how to do. I can't use rankcounter > 5 because I may end up with 0 lines or 33 lines. I can't use rankcounter > max(rankcounter)-5 because I can't use a max if I don't group... 

Idealy, I would do have the rankcounter count backwards, then I could still take rankcounter < 6, but I don't know how to do this.

Could anyone enlighten me? How do I take the 5 last lines, no matter how many lines are in the paragraph, I just need the 5 last. 



(2) By Gert Van Assche (GertVA) on 2021-06-22 10:08:04 in reply to 1.1 [link] [source]

I found a way -- not sure if it is the most efficient, but it works.

I'm creating a second table with a max(rankcounter) column. I then JOIN both tables and use the max(rankcounter) in a formula.