I have the following statement:
SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id
which returns a table like this one for example:
id
1
3
4
5
But what I need is to know the position of a given number. For example 4 has position 2 in the database.
I can do that with normal C code of course but I wonder if I can do everything in SQL, something like this for example:
SELECT ROWNUMBER(id, 4) FROM myTable WHERE aCondition == 1 ORDER BY id
which should return:
ROWNUMBER
2
Best Answer
There is a
ROW_NUMBER()
analytic function but I don't think it's available in SQLite. So, here's a generic approach (which may be quite slow):Note that you should not use the additional two lines (checks), if your final
WHERE
checks for something else thanid
.Rethinking, there's an even better way, because you only want the Row_Number of the last element in that list. There is no need to number them all, we can just count them: