(1.1) By Gert Van Assche (GertVA) on 2021-06-21 21:28:19 edited from 1.0 [link] [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. thanks gert
(2) By Gert Van Assche (GertVA) on 2021-06-22 10:08:04 in reply to 1.1 [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.