Get Row Number according to a specific condition by sqlite

Question

I have 3 Tables:

So I need an SQLite query that shows me the Row Number of the s_id=7 ( it must be here 3 )

I search too much on the Internet to find tips but I didn't find! it will be perfect if someone can help me with that.

Answer 1

You can use window functions like this:

select *
from (
    select s_id, row_number() over(order by s_id) as rn
    from section
) s
where s_id = 7

Or, if your version of SQLite does not support window functions, you can use a subquery:

select s_id,
    (select count(*) from section s1 where s1.s_id <= s.id) as rn
from section s
where s_id = 7