Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following statement:

SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id

which returns a table like this one for example:


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:

share|improve this question
What do you mean with "position 2"? Rows do not have specific order, unless you specify one (do you mean position 2 when ordered by id?) – ypercubeᵀᴹ Apr 13 '12 at 8:34
yes, exactly that. – Emiliano Apr 13 '12 at 8:44
There is a ROW_NUMBER() analytic function but I don't think it's available in SQLite. – ypercubeᵀᴹ Apr 13 '12 at 8:46
I feared that. If there is not such a function then the "C code" is the only way. Thanks anyway. – Emiliano Apr 13 '12 at 8:52
@ypercube: I wanted to say that the OP wanted to retrieve the position of the row in the resultset (wich is sorted by the column id) and not the position of the row in the table. So the row with the rownum=0 is the row with the smallest id that satisfies the condition and not the first row in the table that satisfies the condition. – miracle173 Apr 14 '12 at 11:44
up vote 4 down vote accepted

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):

SELECT pos - 1 AS ROWNUMBER     --- the "-1" is to get the C-style row numbering
         , COUNT(*) AS pos 
    FROM myTable AS a
      JOIN myTable AS b
        ON <= 
    WHERE a.aCondition = 1
      AND b.aCondition = 1
      AND <= 4             --- optional for  
      AND <= 4             --- performace 
  ) AS tmp
WHERE id = 4

Note that you should not use the additional two lines (checks), if your final WHERE checks for something else than id.

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:

SELECT CASE WHEN maxid = 4     --- so we don't get a RowNumber for an @id
              THEN cnt-1       --- that does not meet the condition
              ELSE NULL        --- or is not in the table at all 
  ( SELECT MAX(id)  AS maxid
         , COUNT(*) AS cnt 
    FROM myTable AS a
    WHERE a.aCondition = 1
      AND <= 4   
  ) AS tmp
share|improve this answer
That's perfect. My table is not very big and this solution works very well. – Emiliano Apr 13 '12 at 9:17

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.