Can I get the position of a record in a SQL result table?

Question

If I do something like

SELECT * FROM mytable ORDER BY mycolumn ASC;

I get a result table in a specific order.

Is there a way in SQL to efficiently find out, given a PK, what position in that result table would contain the record with my PK?

Answer 1

On databases that support it, you could use ROW_NUMBER() for this purpose:

SELECT RowNr
FROM (
    SELECT 
         ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr,
         mycolumn
    FROM mytable
) sub
WHERE sub.mycolumn = 42

The example assumes you're looking for primary key 42 :)

The subquery is necessary because something like:

SELECT 
     ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr
FROM mytable
WHERE sub.mycolumn = 42

Will always return 1; ROW_NUMBER() works after the WHERE, so to speak.

Answer 2

There's no way you can tell that without selecting an entire subset of records. If your PK is of integer type, you can

select count(*) from mytable 
    where id <= 10 -- Record with ID 10
    order by mycolumn asc

Answer 3

SQL doesn't work that way. It's set-based, which means that "position in that result table" is meaningless to the database.

You can keep track of position when you map the ResultSet into a collection of objects or when you iterate over it.

Answer 4

You can count the number of records where the value that you are sorting on has a lower value than the record that you know the key value of:

select count(*)
from mytable
where mycolumn < (select mycolumn from mytable where key = 42)

Answer 5

Unfortunately you cannot get "the position of a row in a table".

The best you can get, using ORDER BY and a variant of the ROW_NUMBER construct (depends on the database engine in use), is the position of a row in the resultset of the query executed.

This position does not map back to any position in the table, though, unless the ORDER BY is on a set of clustered index columns, but even then that position might be invalidated the next second.

What I would like to know is what you intended to use this "position" for.