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]

Answer 2

You don't mention which RDBMS you're running, but if you're using SQL Server 2005 or greater, you can employ the ROW_NUMBER() function - like this:

SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) AS RowNumber
FROM   MyTable

Then, to fetch a given row, you can use a derived table or a common table expression - like this:

;WITH NumberedRows
AS
(SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) AS RowNumber
FROM    MyTable)
SELECT RowNumber FROM NumberedRows WHERE id = 47

To sort out "which page" the record is on, you would need to divide the row number by the number of records per page, and round up to the nearest integer.