SQL - how to determine which position/order/count my selected record is in?

Question

Imagine I have a table:

ID     field1       field2
---    -------      ------
111      1          11113
112      1          11114
113      1          44321
114      1          49339
115      2          53234

I'm interested in all records where field1 = 1 - and specifically field2 = 44321 , but I want to know what position it is in my selection of field1=1 (in this case, it'd be 3).

 SELECT * FROM table WHERE field1 = 1 ORDER BY id

will get me all the records I want, but what I want to know is the number 3 (the position in the selection that 44321 is - it's the 3rd record in the query, I want to know that 3).

Is there any elegant query I can do to find out the position of the row I'm particularly interested in, or do I need to cursor fetch and walk through my recordset and find out with some counter++ business?

I know the field1 I want, I know the field2 I want - I just want to know what position field1+field2 is in the greater field1=1 query - that 3, the position).

Answer 1

Try this:

POS column will give the position of the record that you are looking for

WITH qry AS
(
    SELECT a.* 
                 ROW_NUMBER() OVER(ORDER BY id)  POS
        FROM table a
     WHERE field1 = 1 
)
SELECT qry.pos
  FROM qry
 WHERE field2 = 44321 

Answer 2

SELECT COUNT(*)+1
FROM table
WHERE ID<(SELECT TOP 1 ID
          FROM table
          WHERE field1=1
          AND field2=44321)