August 17, 2012 at 10:52 am
I have a table that gets data populated into it along w/a keyid field (incremental), but I need to re-order the rows randomly for another process that will use this table. I know I can do a Select * from Table Order by NewId(), but I want to update or alter the table rows in a random order. Can this be done via UPDATE or ALTER, or is it more involved.
Thx,
John
August 17, 2012 at 11:04 am
Rows in a table don't have a defined order. If you want an order when you retrieve rows from a table you must use the ORDER BY clause, otherwise you get the rows in whatever order the query processor finishes with them, and that can and will vary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 3:19 pm
CELKO (8/17/2012)
... What you have described in your narrative is a deck of punch cards that you want to shuffle.
...
And that could be exactly what he wants to accomplish.
August 18, 2012 at 2:41 pm
That's right Lynn, I did accomplish it. I started by creating a temp table w/ an incremental field. Then created my final table from it ordering by newid. It worked for the solution being implemented. I think this guy celko hasn't been getting any for quite sometime, hey celko you sound very bitter and negative and just full of destructive derogatory criticism. I'll appreciate not getting any replies from you in the future. I'm pretty confident I can get by w/o your help.
August 18, 2012 at 3:55 pm
latingntlman (8/18/2012)
That's right Lynn, I did accomplish it. I started by creating a temp table w/ an incremental field.
Just bear in mind that you need to order by the identity column when retrieving from the temp table, otherwise you may not get the rows in the order you want, it's subject to the whims of the optimiser and what plan the query gets.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply