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.
| Author |
Topic |
|
kieran5405
Yak Posting Veteran
96 Posts |
 Posted - 2008-02-28 : 06:05:14
|
| Hi,Im not sure if this can/should be done through sql...but i have a sql statement and i need the first column returned to start at 1 - and then increment for each row.I am then using the returned values in another app.I tried using count(columnname) but this just stays at 1 for each row.Thanks for an feedback... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-28 : 06:07:36
|
| Use ROW_NUMBER() function. Check BOL for sysntax. Also note that you can use this only in SQL 2005. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-28 : 06:07:48
|
| If it is SQL Server 2005 you can use ROW_NUMBER() function. For SQL server 2000, you can use IDENTITY column.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
kieran5405
Yak Posting Veteran
96 Posts |
Posted - 2008-02-28 : 06:25:46
|
| Thanks for reposnses...its sql 2000...sorry for not saying that.I have tried using the 'identity' column but am unsure how to use it in the select statement i.e. select identity(), firstname from tblname.All the info on it seems to be about creating tables using it etc... But how do I use it in a select statement...with the first row always been 1.Thanks for any feedback... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-28 : 06:27:58
|
quote:
Originally posted by kieran5405
Thanks for reposnses...its sql 2000...sorry for not saying that.I have tried using the 'identity' column but am unsure how to use it in the select statement i.e. select identity(), firstname from tblname.All the info on it seems to be about creating tables using it etc... But how do I use it in a select statement...with the first row always been 1.Thanks for any feedback...
you need to create a temporary table with identity column and insert your query results to the temp table. Then fire a select query to get all data from temp table and identity column will have auto incremented values. |
|
|
|
kieran5405
Yak Posting Veteran
96 Posts |
|
|
|
|
|
incrementing column