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 |
|
josephl
Starting Member
12 Posts |
 Posted - 2008-03-17 : 20:48:26
|
| Hi,I have an existing table (without PK). I want to output my SELECT statement with an auto-increment on the 1st column like this:# Lastname Firstname Address1 Obama Clinton xxxxx2 Hillary xxxx xxxxx...Can someone help me on this pls? Appreciate it.Thanksjoseph |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-18 : 10:24:40
|
| Are you on 2005? If so, you can use ROW_NUMBER()SELECT ROW_NUMBER() OVER(ORDER BY Lastname, Firstname) AS RowId, Lastname, Firstname, AddressFROM TableName |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-18 : 12:13:27
|
| Also your compatability level should be more than 80 for using ROW_NUMBER() |
|
|
|
josephl
Starting Member
12 Posts |
Posted - 2008-03-18 : 15:14:57
|
| Hi, I've tried this before but i got the error 'OVER' is not recognized in the SELECT statement. I'm using MSSQL2000, also what do you mean by compatability level?Thank you all. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-18 : 16:22:57
|
| You posted on the SQL 2005 forum, so the answers you got are for SQL 2005.CODO ERGO SUM |
|
|
|
josephl
Starting Member
12 Posts |
Posted - 2008-03-18 : 16:33:30
|
| Oh, sorry, I didn't notice it. Anyway, I already got the solution but If someone canprovide a more simple approach, it would be greatly appreciated:DECLARE @tCat TABLE (TID int identity(1,1), CategoryID int, CategoryName varchar(100))INSERT @tCat (CategoryID, CategoryName )SELECT CategoryID, CategoryNameFROM dbo.CategoriesI was hoping that the query has no temp table. Is there any more simple approach aside from this? else this would be good enough.Thanks you all! |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-03-19 : 03:59:40
|
| There are other ways but not as simple and less efficient. |
|
|
|
|
|
|
|
|
Autoincrement column