Random results - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Random results

Author

Topic

ultradiv
Starting Member

41 Posts

Posted - 2011-01-23 : 07:14:22

Hi folks
Any way to randomise an ordered select list?

CREATE TABLE #TESTSORTING (ID INT ,GroupedCol INT)

INSERT INTO #TESTSORTING
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,1

SELECT 'This is the NON desired output:' comment

SELECT * FROM #TESTSORTING ORDER BY ID ASC

SELECT 'This is the actual output that CAN include the above:' comment

SELECT * FROM #TESTSORTING ORDER BY NEWID()

Drop Table #TESTSORTING

This produces a fine randomized (enough for my needs) list.
But I need the GroupedCol to remain ordered during this process thus NEVER like this :
ID GroupedCol
3 1
2 2
1 1

Only like this (ignoring the ID's where GroupedCol=1):
ID GroupedCol
3 1
1 1
2 2

or this :
ID GroupedCol
2 2
1 1
3 1

I hope i have made sense

Thnx
Andy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-23 : 07:33:25

order by GroupedCol, NEWID()

KH
[spoiler]Time is always against us[/spoiler]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-23 : 07:35:24

Here is an article which may help: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5931

Poor planning on your part does not constitute an emergency on my part.

ultradiv
Starting Member

41 Posts

Posted - 2011-01-23 : 11:16:51

I don't think I have made myself clear, very sorry.

What I am trying to do is this;

When you use order by [col] you effectively "group" rows when those [col]'s hold the same data in other rows. I want to randomize those "groups" in the returned table, so in my simplistic example the rows returned in GroupedCol would be
1
1
2

Or

2
1
1

Never

1
2
1

@khtan thanx but this orders the results without any random element other than within the "groups" in the example.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-23 : 22:27:25

is this what you want ?


ORDER BY case when abs(checksum(newid())) % 2 = 0 then GroupedCol * -1 else GroupedCol end

KH
[spoiler]Time is always against us[/spoiler]

ultradiv
Starting Member

41 Posts

Posted - 2011-01-24 : 03:53:05

@khtan yes! Thankyou that seems to work just fine.
Can you explain to me how it works please?

Many thanks
Andy

ultradiv
Starting Member

41 Posts

Posted - 2011-01-24 : 04:05:31

Although it does not continue to work when the quantity of "groups" increases :

CREATE TABLE #TESTSORTING (ID INT ,GroupedCol INT)

INSERT INTO #TESTSORTING
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,4 UNION ALL
SELECT 7,4 UNION ALL
SELECT 8,1

SELECT * FROM #TESTSORTING ORDER BY case when abs(checksum(newid())) % 2 = 0 then GroupedCol * -1 else GroupedCol end

Drop Table #TESTSORTING

ultradiv
Starting Member

41 Posts

Posted - 2011-01-24 : 09:56:09

I found a solution:

;WITH RANDOMIZER AS
(
SELECT GroupedCol, MIN(ABS(CHECKSUM(NEWID()))) NWID
FROM #TESTSORTING
GROUP BY GroupedCol
)
SELECT TS.*
FROM #TESTSORTING TS
INNER JOIN RANDOMIZER RND
ON TS.GroupedCol = RND.GroupedCol
ORDER BY RND.NWID , TS.GroupedCol

Thanks everyone for your help it is much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources