tag:blogger.com,1999:blog-16771259.post116009665716700920..comments2024-03-07T03:02:45.934-08:00Comments on SQL Server Code,Tips and Tricks, Performance Tuning: SQL Challenge: Random Grouping Of DataUnknownnoreply@blogger.comBlogger8125tag:blogger.com,1999:blog-16771259.post-1160512402121737332006-10-10T13:33:00.000-07:002006-10-10T13:33:00.000-07:00I came up with a very similar solution to the sec...I came up with a very similar solution to the second rob f solution:<BR/><BR/>SELECT T.*<BR/>FROM (SELECT DISTINCT [name], SUBSTRING([name], CAST(rand() * 10 AS INT), 1) AS 'OrderCol'<BR/> FROM #Testcompanies<BR/> ) I<BR/>JOIN #Testcompanies T<BR/> ON T.Name = I.Name<BR/>ORDER BY I.OrderCol<BR/><BR/>Regards<BR/><BR/>FTDAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160459651861900412006-10-09T22:54:00.000-07:002006-10-09T22:54:00.000-07:00Actually, try this one - this one is totally legit...Actually, try this one - this one is totally legit - not using any workarounds at all:<BR/><BR/>select c.*<BR/>from<BR/> (<BR/> select name, max(checksum(newid())) as order1<BR/> from #TestCompanies<BR/> group by name<BR/> ) c2<BR/> join<BR/> #TestCompanies c<BR/> on c.name = c2.name<BR/>order by c2.order1, c.idAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160459522020511592006-10-09T22:52:00.000-07:002006-10-09T22:52:00.000-07:00I should've come across this post earlier, but any...I should've come across this post earlier, but anyway... this is probably similar to ones you already have. It uses the cheat of having top with a large number to materialise the derived table. Without it, the optimiser applies newid() to each row of 6, not of 3.<BR/><BR/><BR/>select c.*<BR/>from<BR/> (<BR/> select top 2147483647 name, newid() as order1<BR/> from<BR/> (select name<BR/> from #TestCompanies<BR/> group by name) c1<BR/> ) c2<BR/> join<BR/> #TestCompanies c<BR/> on c.name = c2.name<BR/>order by c2.order1, c.idAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160150572759636862006-10-06T09:02:00.000-07:002006-10-06T09:02:00.000-07:00Hi Denis, The second solution is really cool. But...Hi Denis,<BR/> The second solution is really cool. <BR/><BR/>But the first one might not work. <BR/>And its an undocumented behavior.<BR/><BR/>Its actually based on the query execution plan. The GroupedOrder may be evaluated at the time of ordering of the final result set. <BR/>The same advice I got from Hugo when I came up with the concatenation of column in my blog :) <BR/><BR/>Just check it out.. <BR/><BR/>-Best regards<BR/>OmnibuzzOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160140402301778922006-10-06T06:13:00.000-07:002006-10-06T06:13:00.000-07:00I have posted the 2 solutions I had in mindI have posted the 2 solutions I had in mindDenishttps://www.blogger.com/profile/13745938552201273794noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160133775503841992006-10-06T04:22:00.000-07:002006-10-06T04:22:00.000-07:00Maybe this.. Looks too ugly to me though :)select ...Maybe this.. Looks too ugly to me though :)<BR/><BR/>select a.name,a.id<BR/>from <BR/>#Testcompanies a<BR/>ORDER BY cast(abs(checksum(name))*rand() as int) % (select count(*) from #Testcompanies),name,<BR/>newid()Omnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160132986283273262006-10-06T04:09:00.000-07:002006-10-06T04:09:00.000-07:00Okay fair enough, but now try without temp tables ...Okay fair enough, but now try without temp tables or table variablesDenishttps://www.blogger.com/profile/13745938552201273794noreply@blogger.comtag:blogger.com,1999:blog-16771259.post-1160132325874614432006-10-06T03:58:00.000-07:002006-10-06T03:58:00.000-07:00Two solutions I thought of:-- Using a temporary ta...Two solutions I thought of:<BR/><BR/>-- Using a temporary table<BR/>SELECT name, NEWID() sortBy INTO #OrderBy FROM<BR/>(SELECT name FROM TestCompanies GROUP by name) A<BR/><BR/>SELECT A.name, A.id FROM TestCompanies A INNER JOIN<BR/>#OrderBY B ON A.name=B.name<BR/>ORDER BY sortBy, A.id<BR/><BR/>DROP TABLE #OrderBy<BR/><BR/>-- Or using a table variable<BR/>DECLARE @OrderBy TABLE (name varchar(50), sortBy uniqueidentifier)<BR/><BR/>INSERT INTO @OrderBy (name, sortBy)<BR/>SELECT name, NEWID() FROM (SELECT name FROM TestCompanies GROUP by name) A<BR/><BR/>SELECT A.name, A.id FROM TestCompanies A INNER JOIN<BR/>@OrderBY B ON A.name=B.name<BR/>ORDER BY sortBy, A.idAnonymousnoreply@blogger.com