Auto-ID in SELECT statement

johnywhite
johnywhite used Ask the Experts™
on
Is there any way to create an auto-id column in my SELECT statement.  I have an ID column in my table, however, what I want to do is have a column that always begins with 1 in every select statement. So if I select 10 records it will have 1 through 10 as the ID for the column.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
Two ways
1. Insert those values into a table variable having an identity column and select from there
2. In case u have some primary key on that table, you can use a seld join to do this

Commented:
What you are thinking of is rank.

Using Adventureworks

SELECT c1.nationalidnumber,
      ( SELECT COUNT(*)
          FROM humanresources.employee c2
         WHERE c2.nationalidnumber <= c1.nationalidnumber ) AS "rank"
  FROM humanresources.employee c1
 ORDER BY c1.nationalidnumber ;

It is much easier to do in 2005
AneeshDatabase Consultant
Top Expert 2009

Commented:
-- here is an example of part2
declare @tab table( i int)
insert into @tab select 1 union select 3 union select 2 union select 6 union select 4
SELECT ident= (SELECT COUNT(*) from  @tab a where a.i<b.i )+1,I
from @tab b
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015

Commented:
The row_number() function in 2005's might be what you're looking for.  Its not a unqiue id, just a sequential row number.

http://msdn2.microsoft.com/en-us/library/ms186734.aspx
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Author

Commented:
Sorry to be just responding to this. My wife just had a baby (it's our first) and life got flipped upside down. Anyway, so I tried the method ptjcb posted and the problem with that is I need to be able to sort the data and then have it count up from the sorted table. As for agx, I tried yours. but I am using the Visual Studio 2005 interface with SQL Server Express 2005 and apparently Visual Studio won't accept the row_number command, even though from what I read it is valid, apparently it is a bug.
aneeshattingal - I didn't understand your part 2, and couldn't get it to work either, but your first idea should work just fine for me. But I don't know how to create a table variable. Do you have an example?
Commented:
declare @t table (column1 int )

SELECT * FROM @t
Most Valuable Expert 2015

Commented:
@johnywhite - Congratulations :)

Commented:
Yes, I forgot to type that also.

Congratulations

Author

Commented:
Thank you very much for all of your help, that worked perfectly! And thanks for the congrats!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial