select rowid?

• memymasta

  SSCrazy

  Points: 2373

  More actions

  December 1, 2011 at 5:19 am

  #251379

  Hey, i wonder what's the most efficient way to get a "rank" of a record in a table set.

  An example:

  If i select * from user -> SELECT * FROM #user ORDER BY id ASC

  But i would like to know what rank 'Duke' have in that specific set.

  CREATE TABLE #user

  (id int identity(1, 1),

  userid int,

  firstname VARCHAR(20) NULL,)

  insert into #user (userid, firstname)VALUES (4, 'Duke')

  insert into #user (userid, firstname)VALUES (5, 'David')

  insert into #user (userid, firstname)VALUES (6, 'Roger')

  SELECT * FROM #user

  DROP TABLE #user

  In this case it should return value 1

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 1, 2011 at 5:23 am

  #1415176

  Not sure I know what you're asking. Does this help?

  BEGIN TRAN

  CREATE TABLE #user

  (id int identity(1, 1),

  userid int,

  firstname VARCHAR(20) NULL,)

  INSERT INTO #user (userid,firstname) VALUES (4,'Duke')

  INSERT INTO #user (userid,firstname) VALUES (5,'David')

  INSERT INTO #user (userid,firstname) VALUES (6,'Roger')

  SELECT rowid, id, userid, firstname

  FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS rowid,

  id, userid, firstname

  FROM #user) a

  WHERE firstname = 'Duke'

  ROLLBACK

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• memymasta

  SSCrazy

  Points: 2373

  More actions

  December 1, 2011 at 7:40 am

  #1415264

  Cadavre (12/1/2011)

  ---

  Not sure I know what you're asking. Does this help?

  Ty, that was exactly what i was looking for!