How to determine position of row in sql result-set?
count, sql
Solution
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]
Problem
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