Find index of row among result set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find index of row among result set

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-11-10 : 06:39:35

Given the following basic query:


SELECT 
  * 
FROM
  tbl_attendees
WHERE
  (eventID = 5) AND
  (approved = 1) 
ORDER BY
  dateRegistered

I get these results (simplified for brevity):


UserID   eventID   approved   dateRegistered
--------------------------------------------
4          5          1       2011-01-01
75         5          1       2011-01-06
19         5          1       2011-01-07
14         5          1       2011-01-17
18         5          1       2011-01-25

It would be useful at the same time to also return a value that showed the index number of one of those UserID values. So, UserID 19 would return 2 (the third entry in the list counting from zero, or 3 counting from 1).

Is this possible to do?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 06:44:04

SELECT
* , seq = (row_number() over (order by dateRegistered))-1 -- could use rank() here
FROM
tbl_attendees
WHERE
(eventID = 5) AND
(approved = 1)
ORDER BY
dateRegistered

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-11-15 : 11:18:59

That's exactly what I'm looking for, and I learned something new - so thank you twice over... :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources