How to Assign ID Randomly - 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 2005 Forums

Transact-SQL (2005)

How to Assign ID Randomly

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2012-04-19 : 07:02:28

Dear All,

I am in great trouble. I am having 2 Tables: QuestionPaper & Employee.

The QuestionPaper is having following details:


--------------------------------
QuestionID | ExamID | SectionID
--------------------------------
232        |   18   |    20
233        |   18   |    20
234        |   18   |    20
235        |   18   |    20
236        |   18   |    20
237        |   18   |    20
238        |   18   |    20
239        |   18   |    20
240        |   18   |    20
241        |   18   |    20
262        |   18   |    21
263        |   18   |    21
264        |   18   |    21
265        |   18   |    21
266        |   18   |    21
267        |   18   |    21
268        |   18   |    21
269        |   18   |    21
270        |   18   |    21
271        |   18   |    21
292        |   18   |    22
293        |   18   |    22
294        |   18   |    22
295        |   18   |    22
296        |   18   |    22
297        |   18   |    22
298        |   18   |    22
299        |   18   |    22
300        |   18   |    22
301        |   18   |    22
--------------------------------

and the Employee table contains:


---------------
ExamID | EmpID
---------------
  18   |  119
  18   |  219
  18   |  359
  18   |  297
  18   |  384
  18   |  245
  18   |  117
  18   |  269
---------------

Now from the Above 2 tables, I have to create a T-SQL, which will assign 5 Unique QuestionID RANDOMLY as per SectionID and ExamID to each EmpID, e.g.


----------------------------------------
ExamID | EmpID | SectionID | QuestionID
----------------------------------------
  18   |  119  |    20     |    233
  18   |  119  |    20     |    237
  18   |  119  |    20     |    232
  18   |  119  |    20     |    241
  18   |  119  |    20     |    239
  18   |  119  |    21     |    262
  18   |  119  |    21     |    269
  18   |  119  |    21     |    265
  18   |  119  |    21     |    268
  18   |  119  |    21     |    263
  18   |  119  |    22     |    301
  18   |  119  |    22     |    299
  18   |  119  |    22     |    297
  18   |  119  |    22     |    295
  18   |  119  |    22     |    293
  18   |  219  |    20     |    236
  18   |  219  |    20     |    233
  18   |  219  |    20     |    241
  18   |  219  |    20     |    232
  18   |  219  |    20     |    239
  18   |  219  |    21     |    265
  18   |  219  |    21     |    266
  18   |  219  |    21     |    267
  18   |  219  |    21     |    268
  18   |  219  |    21     |    269
  18   |  219  |    22     |    292
  18   |  219  |    22     |    294
  18   |  219  |    22     |    296
  18   |  219  |    22     |    298
  18   |  219  |    22     |    300
----------------------------------------

Hope I can make you understand my query, please help.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-19 : 08:55:36

Does this work?


SELECT
	e.[EmpID]
	, e.[ExamID]
	, rqp.[SectionID]
	, rqp.[QuestionID]
FROM
    Employee AS e
    CROSS APPLY (
		SELECT
			[QuestionID]
			, [ExamID]
			, [SectionID]
			, [RowNo] = ROW_NUMBER() OVER ( PARTITION BY [SectionID] ORDER BY NEWID() ) 
		FROM
			QuestionPaper AS qp
		WHERE
			qp.[ExamID] = e.[ExamID]
		)
		AS rqp
WHERE
	rqp.[RowNo] <= 5

It's pretty scrubby......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 08:56:23

[code]
SELECT e.*,e1.SectionID,e1.QuestionID
FROM Employee e
CROSS APPLY (SELECT ROW_NUMBER() OVER (PARTITION BY SectionID ORDER BY NEWID()) AS Rn,*
FROM QuestionPaper
WHERE ExamID = e.ExamID
)e1
WHERE Rn <=5
ORDER BY e.EmpID,e.ExamID,e1.SectionID,e1.QuestionID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2012-04-20 : 03:23:17

Thanks a lot visakh16 and Transact Charlie...

Both of yours T-SQL worked smoothly.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:33:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources