November 24, 2003 at 8:49 am
Does anyone have a bit of code to return the rows in a table (ORDER BY, primary key, whatever order) with a generated row number that is NOT selected from an IDENTITY column. E.g., The SELECT statment FROM tablename would yield two columns where the 1st column is generated "on the fly", and the 2nd column is selected from the table: 1 securityabc, 2 securityxyz, 3 securityrst.
Regards, Melissa
November 24, 2003 at 8:52 am
http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 24, 2003 at 9:53 am
Thank you! Exactly what I was looking for. 🙂
Regards, Melissa
November 25, 2003 at 1:10 pm
OK, MS is out of date; using a subquery is faster. Like this:
Select t.tname, t.tid,
(
Select count(t1.tid)
From #test t1
where t1.tName < t.tName--This is where you signify the rank order
) NameRank
From #test t
----------------Test Data------------------
if object_ID('tempdb..#test') is not null drop table #test
create table #test (Tid int identity, tname char(3) Not Null Primary Key)
Insert #test Values ('ZZZ')
Insert #test Values ('GGG')
Insert #test Values ('HHH')
Insert #test Values ('III')
Insert #test Values ('DDD')
Insert #test Values ('CCC')
Insert #test Values ('BBB')
Insert #test Values ('AAA')
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply