count - How to determine position of row in sql result-set?

Question

i have a sql query:

select id, name from table order by name

result looks like this:

52 arnold 
33 berta 
34 chris 
47 doris
52 emil

for a given id=47 how can i determine the position in the result set? the result should be 4 because:

52 arnold
33 berta
34 chris

are before (47, doris) and id=41 is on the 4th position in the result set.

How to do this in SQL? How in HQL? In a pagination example, do i have to execute 2 statements or is there a solution where i can retrieve exactly that window which contains the row with id=47?

postgreSQL and java

Answer 1

The previous posts are correct. Use ROW_NUMBER if using Microsoft SQL Server 2005 or greater.

However, your tags do not specify that you're using MSSQL, so here's a solution that should work across most RDBMS implementations. Essentially, use a correlated subquery to determine the count of rows in the same set that are less than the current row, based on the values of the ORDER clause of the outer query. Something like this:

SELECT      T1.id,
            T1.[name],
            (SELECT COUNT(*) 
             FROM table T2 
             WHERE T2.[name] < T1.[name]) + 1 AS rowpos
FROM        table T1
ORDER BY    T1.[name]