InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:57:14 GMT20http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxThis is about 3 years too late, but it worked for me:SELECT [Field1] , ROW_NUMBER() OVER (ORDER BY [Field1]) AS 'Row Number' FROM [dbo].[MyTable]Wed, 02 Mar 2011 09:35:55 GMTrenrobhttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxI removed the top 1000 and it worked.I added abs to insure a positive number.abs(checksum(newID()));)Fri, 25 Jan 2008 11:48:42 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspx[quote][b]Debra Owens (1/25/2008)[/b][hr]After running several test the top 1000... will not work with the query.The user name and date is being queried, member quired is not one of the 1000 returned therefore, no items selected.When I run it with the udf random it works.[/quote]Debra - the "top 1000" was there for my purposes (the test table was rather large). I just wanted to highlight the Checksum(NewID()) method....You absolutely don't need the TOP n syntax if your case doesn't lend itself to it.Fri, 25 Jan 2008 11:41:50 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspx[quote][b]GSquared (1/25/2008)[/b][hr]checksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.[/quote]Per BOL:[quote]CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change.[/quote]From the way I read that it, running a checksum against unique values will return unique values. That's what a hash function does, after all: it returns a unique value that can be used to determine equality. If the hash codes are equal, then so are the values. NEWID() does in fact guarantee uniqueness, so I'm relying on that.From testing, I've generated sets of 6 million unique numbers using that and have yet to run into a conflict.Am I misreading that somehow? I am making some assumptions, but I thought they were pretty solid.Fri, 25 Jan 2008 11:38:27 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxAfter running several test the top 1000... will not work with the query.The user name and date is being queried, member quired is not one of the 1000 returned therefore, no items selected.When I run it with the udf random it works.Fri, 25 Jan 2008 11:21:16 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxchecksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.Fri, 25 Jan 2008 11:03:20 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxThat works also.I'll use your since!Thanks!!Fri, 25 Jan 2008 10:46:05 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxAnd - there's NO guarantee that it won't return the same number twice... The larger the set of numbers, the more likely it WILL duplicate the number.Try this instead:[code]select top 1000 checksum(newID()),* from myTable[/code]Fri, 25 Jan 2008 09:34:28 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxGot it!!!1. create a view CREATE VIEW vRandNumberASSELECT RAND() as RandNumber2. create a udfCREATE FUNCTION RandNumber()RETURNS floatAS BEGIN RETURN 7 + (SELECT RandNumber FROM vRandNumber) * 113.00 END3. add it to viewSELECT name, date, replace(dbo.RandNumber(),'.','') 'cnt',.....Got this from http://weblogs.sqlteam.com/jeffs/jeffs/archive/2004/11/22/2927.aspxFri, 25 Jan 2008 09:20:53 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxDebraIf you don't have more than one update in any particualr second, you could do something like this:select datediff(s,'01 Jan 1980',getdate())If your updates are more frequent than that, you could try multiplying by 1000, adding the milliseconds, and casting as bigint.Good luckJohnFri, 25 Jan 2008 09:18:40 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxHas to be numeric....Fri, 25 Jan 2008 08:40:13 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxDebraHave you tried NEWID()?JohnFri, 25 Jan 2008 08:34:31 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxI tried something like this....cast(rand()*DATEPART(ms,getdate())*300 as int).. it creates a number however the numbers are identical.I need something that creates a unique number. It does not have to be sequencial just unique per row.:cool:Fri, 25 Jan 2008 08:17:52 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxThere isn't an exact solution that I know of. Since a view can't pass parameters to a table-value function, you might have trouble doing this.If you can use a table-value function instead of a view, here's a possible solution:[code]create function udf_NameAccountZip ()returns @NAZ table (Name varchar(25),Account int,Zip char(10),RowNumber int identity primary key)asbegin insert into @naz(name, account, zip) select name, account, zip from (... table(s) here...) where ... order by ...returnend[/code]You'll need to modify it to take input parameters.If that won't work (if you can't change the front-end code from accessing a view to accessing a udf, for example), I can't think of a solution for SQL 2000.Fri, 25 Jan 2008 07:28:12 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxJohn, thanks for the quick response.I do not need to update a table on display data via a view.More info:The view will be used by a IVR.The user will enter a date the IVR program will query the viewwith user name and date.The view returns, for example, 10 items with a matching date.The IVR program needs a counter to loop through the rows.Does that help?Fri, 25 Jan 2008 07:07:39 GMTdowneshttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxDebraI think the simplest way to achieve what you want is with an identity column. If this is a new table, add the identity column before you insert any data. If you already have data in your table, create a new table with an identity column, and insert the data from your existing table into it.JohnFri, 25 Jan 2008 07:01:44 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxBelow is my attempt - wrong!!alter PROCEDURE sp_GetCounter(@ccnt int) asdeclare @counter intset @counter = @counter+@ccntreturn @ccnt -------------------------------------------Just a thought, why r u returning @ccnt when ur incrementing @counter in ur Proc.?Fri, 25 Jan 2008 07:01:43 GMTMayank Khatrihttp://www.sqlservercentral.com/Forums/Topic447477-8-1.aspxI need a row counter.I have a view that displays the following:NameAccountZipI need a UDF that will incremented number and return it to the view:Name Account Zip counterjone 97000 33805 1smith 85000 33501 2etc.. 3,4,5,......:cool:Below is my attempt - wrong!!alter PROCEDURE sp_GetCounter(@ccnt int) asdeclare @counter intset @counter = @counter+@ccntreturn @ccntFri, 25 Jan 2008 06:50:19 GMTdownes