| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-07-23 : 12:02:00
|
| I had a post asking about an Update within a function... (Guess thats out the window)The question is: How can I auto increase a number for each record in a select statement? (Such as a row number)IE:RN NAME-- ----01 Bill02 Jill03 DaveI do not need to keep these numbers in the DB (with the exception of which was the last number used)Any suggestions?DanielSQL Server DBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 12:08:03
|
tons of ways. Variable method (please correct me if I'm horribly wrong):I renamed the column name. Pain in the ass using words like that.declare @Tablename table (IDcol identity(1,1), ColName)insert into @tablename (colname)select names from othertablethere.. now you have a table variable with all your records with an auto incremental ID beside them all. Use a temp table if you'd prefer Or as I always say... Do what Rob says -----------------------Take my advice, I dare yaEdited by - M.E. on 07/23/2002 12:08:55 |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 12:16:51
|
quote:
Did you search SQL Team for "row number"?http://www.sqlteam.com/SearchResults.asp?SearchTerms=row+number
Thanks RobI guess I can build something based off of that but its just crap...What I need is a unique ID for records that come in from our host. We do not store the data here, we only report it. I wanted a table that had a single record that I could update with a function and simply call in a select statement but NOOO Microsoft says that I have to jump through hoops. lol.DanielSQL Server DBA |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 12:17:33
|
| This question comes up a lot, and it is grounded in misunderstanding. What is a Row Number? What does that mean?<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-23 : 12:18:44
|
quote:
What I need is a unique ID for records that come in from our host.
Uhhhh, what about NEWID()/uniqueidentifier? |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 12:20:49
|
quote:
We do not store the data here, we only report it.
How do you report on data that isn't stored?<O> |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 12:52:05
|
quote:
quote:
We do not store the data here, we only report it.
How do you report on data that isn't stored?<O>
This data is pulled from our host and passed in a specific format to a client. The ID must be in a specific format and we cannot store the data here. I can do all this but I was just hoping for a simple way around things like a UDF that I could call in the query and give me an ID in the format needed without messing with creating tables and all sorts of other things. This would have worked but Microsoft does not allow UPDATE in a UDF. BAH! What I need to do is change the requirements for this task...or rethink how this should be done.DanielSQL Server DBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-23 : 13:01:27
|
| How about posting the table(s) you have, and whatever code you're using now?Also, what about NEWID()? Do you need something that is unique, or sequential? If sequence doesn't matter but you need a numeric, you can always CONVERT() the NEWID to a binary and do some bitwise stuff to it.How many rows do you anticipate coming through this? If it's under a billion, then NEWID with some math will handle this very easily. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 13:18:19
|
quote:
How about posting the table(s) you have, and whatever code you're using now?Also, what about NEWID()? Do you need something that is unique, or sequential? If sequence doesn't matter but you need a numeric, you can always CONVERT() the NEWID to a binary and do some bitwise stuff to it.How many rows do you anticipate coming through this? If it's under a billion, then NEWID with some math will handle this very easily.
It has to be a 10 digit id starting with 80 for the first 2 digits and cannot contain alpha. It can be sequential and the next days data cannot contain the same id's (It has to be unique). I would have rather done this in a single query but if I cant then I cant. I think I have it pretty much worked out the long way, but if this could be done with a single query then that would be pretty neat.DanielSQL Server DBAEdited by - sqlserverdba_dan on 07/23/2002 13:25:54 |
|
|
|
|
HOW TO:? New ID for each record in a select