Randomising / Shuffling values in a results set

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 2005 Forums

Transact-SQL (2005)

Randomising / Shuffling values in a results set

Author

Topic

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 09:34:40

Hi guys,

I have a problem that I need to overcome (using a stored procedure) that will take a given "ExamID" and shuffle the "SortOrder" values for the records relating to the given Exam.

To illustrate what I'm trying to achive:


QuestionID, ExamID,  SortOrder
5,          1,       1
7,          1,       2
8,          1,       3

Shuffle stored procedure to reorder all records from the results of "select * from question where examid=x" produces:


QuestionID, ExamID,   SortOrder
5,          1,        2
7,          1,        1
8,          1,        3

So basically I need to move the 'SortOrder' field values randomly across rows with the same ExamID.

I have found many articles on how to select whole random rows and to generate random numbers, but these don't quite achieve what I'm after. I think I need to be able to generate random numbers between 1 and MAX(SortOrder) for each row, but exclude numbers that have already been randomly chosen.

Many thanks in advance :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 09:38:33

Try:-
SELECT QuestionID,
ExamID,
(SELECT SortOrder FROM question WHERE ExamID=q.ExamID ORDER BY NEWID())
FROM question q

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 09:51:15

Thanks for the suggestion!

The line

SELECT SortOrder FROM question WHERE ExamID=q.ExamID ORDER BY NEWID()

works well and produces random order of records. However the full code creates the following error.


SELECT QuestionID,
ExamID,
(SELECT SortOrder FROM question WHERE ExamID=q.ExamID ORDER BY NEWID()) 
FROM question q where examid=x

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I am giving it some thought :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 10:01:24

make it like this & try:-

SELECT QuestionID,
ExamID,
(SELECT TOP 100 PERCENT SortOrder FROM question WHERE ExamID=q.ExamID ORDER BY NEWID())
FROM question q where examid=x

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 10:05:18

Thanks for the reply; I had no idea you could use "TOP 100 PERCENT" this may come in useful!

Unfortunately it produces this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I had already tried running the query with "Select TOP [high number]" :)

-- Btw: It will probably help to know I'm using MS SQL 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 10:09:39

Try making it 1

SELECT QuestionID,
ExamID,
(SELECT TOP 1 SortOrder FROM question WHERE ExamID=q.ExamID ORDER BY NEWID())
FROM question q where examid=x

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 10:16:42

Unfortunately this generates non-unique numbers (as it doesn't remove them from the list it's picking from randomly).

The results are similar to this:

ID Exam SortOrder
-----------------
216 56 4
217 56 4
218 56 5
219 56 4
220 56 5
------------------
216 56 4
217 56 2
218 56 2
219 56 1
220 56 2
-----------------

The sort orders need to be unique.

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 10:22:23

I'm almost there!

DECLARE @t1 TABLE (ID int, SortOrder int)
INSERT @t1
SELECT ID, SortOrder FROM Question WHERE ExamID=56

DECLARE @t2 TABLE (ID int IDENTITY(1,1),SortOrder int)
INSERT @t2
SELECT SortOrder FROM question WHERE ExamID=56 ORDER BY NEWID()

SELECT * FROM @t1 JOIN @t2 ON @t1.ID = @t2.SortOrder

Produces:


@t1 (in order, unshuffled):
ID      SortOrder
216	1
217	2
218	3
219	4
220	5


@t2 (shuffled):
Index   SortOrder
1	2
2	1
3	5
4	3
5	4

I then just need to join the two results on the incrementing numbers.

Unfortunately the JOIN in the last line doesn't work - perhaps you cannot join Table variables??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 10:25:46

Try:-
SELECT * FROM @t1 tbl1 JOIN @t2 tbl2 ON tbl1.ID = tbl2.SortOrder

sdp
Starting Member

12 Posts

Posted - 2008-01-25 : 10:27:05

Got it!


DECLARE @t1 TABLE (ID int, SortOrder int)
INSERT @t1
SELECT ID, SortOrder FROM Question WHERE ExamID=56

DECLARE @t2 TABLE (ID int IDENTITY(1,1),SortOrder int)
INSERT @t2
SELECT SortOrder FROM question WHERE ExamID=56 ORDER BY NEWID()

SELECT t1.ID, t2.SortOrder FROM @t1 t1 JOIN @t2 t2 ON t1.SortOrder = t2.ID

Thanks for your help, visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-25 : 10:29:23

You are welcome. Feel free to come back whenever you face any difficulty.

sarangpitale
Starting Member

3 Posts

Posted - 2008-11-26 : 01:43:43

nice script.
I was looking for a similar solution where i wanted to shuffle values of a column for every record in the table.

Ex. Table Emp having columns EmpID,EmpName
I wanted to shuffle values for column EmpName for every EmpID.
Can someone help here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 02:02:08

quote:
Originally posted by sarangpitale

nice script.
I was looking for a similar solution where i wanted to shuffle values of a column for every record in the table.

Ex. Table Emp having columns EmpID,EmpName
I wanted to shuffle values for column EmpName for every EmpID.
Can someone help here.

what do you mean by shuffle names for empid?for each id wont you be having a unique name?
anyways,post some sample data to illustrate what you want. Also try to post as a new thread rather than appending to previous thread next time as it will improve viewership and you might get solutions quicker.

sarangpitale
Starting Member

3 Posts

Posted - 2008-11-26 : 03:57:50

Thank you for the quick reply visakh

I have a table EmpInfo with two columns EmpID, EmpName.

EmpID EmpName
1 Tom
2 Dick
3 Harry
4 Matt
and so on. i have around 5000 records in this table.

I want to create a stored procedure where the values in the EmpName field are shuffled to reflect incorrect data. After shuffling the table should look like this.

EmpID EmpName
1 Harry
2 Matt
3 Dick
4 Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 04:21:28

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS EmpID,EmpName
FROM Table)t
ORDER BY EmpID[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 04:43:34

A generic solution... Just in case EmpID's are not consecutive nor starts with 1.

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		EmpID INT,
		EmpName VARCHAR(20)
	)

-- Populate sample data
INSERT	@Sample
SELECT	1, 'Tom' UNION ALL
SELECT	2, 'Dick' UNION ALL
SELECT	3, 'Harry' UNION ALL
SELECT	4, 'Matt'

-- Show original data
SELECT	*
FROM	@Sample

-- Display "randomized" data
SELECT		t1.EmpID,
		t2.EmpName
FROM		(
			SELECT	EmpID,
				ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID 
			FROM	@Sample
		) AS t1
INNER JOIN	(
			SELECT	EmpName,
				ROW_NUMBER() OVER (ORDER BY NEWID()) AS recID 
			FROM	@Sample
		) AS t2 ON t2.recID = t1.recID

E 12°55'05.63"
N 56°04'39.26"

sarangpitale
Starting Member

3 Posts

Posted - 2008-11-26 : 05:10:43

thanks peso. thats exactly what i was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources