CODEWITHSUNDEEP
sqlsql-serversql-server-2012shuffle
Aakash
Aakash

Reputation: 179

Shuffle records and sequential records in sql server

I have written a SQL query which returns me a shuffled data by using NEWID() in SQL Server 2012.

Scenario: I have a table in which we have questions in table - "tblquest" whereas in "tblquestLinked" table we have a linked question which is related to main question in "tblquest" table, Now the below query outputs the shuffled data correctly.

select ROW_NUMBER() Over (Order by newid()) as sNo,*
from (select q.ID AS [QID], q.Question,
q.Solution,
isnull(q.IsLinked,0) as IsLinked, ql.LinkQuestion
from tblquest q
left join tblQuestLinked ql
on q.ID = ql.QID) a

enter image description here

I want the dataset returned by query should also have a linked question in it, But should not be shuffled, Instead should be very next row to linked "main" question.

EDIT

As these set of questions will be presented to an Online Examination application, the shuffling of the questions is must.

One Main question can have 0 to many Linked questions. And the linked question appears very next row to that corresponding 'Main" Question. As this will be passed to UI and it will provide the questions based on sNO ( Serial No)

Please find the screenshot (desired result):

enter image description here

Schema Scripts : 

CREATE TABLE [dbo].[tblQuest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IsLinked] [bit] NULL,
[Question] [nvarchar](500) NULL,
[Solution] [nvarchar](500) NULL,
CONSTRAINT [PK_tblQuest] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

CREATE TABLE [dbo].[tblQuestLinked](
[ID] [int] IDENTITY(1,1) NOT NULL,
[QID] [int] NULL,
[LinkQuestion] [nvarchar](max) NULL,
[CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_tblQuestLinked] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO


INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 1 ', 'Solution 1 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 2 ', 'Solution 2 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 3 ', 'Solution 3 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 4 ', 'Solution 4 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 5 ', 'Solution 5 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (0, N'Which of ... 6 ', 'Solution 6 ... ')
INSERT [dbo].[tblQuest] ([IsLinked], [Question], [Solution]) VALUES (1, N'Which of ... 7 ', 'Solution 7 ... ')


INSERT [dbo].[tblQuestLinked] ( [QID], [LinkQuestion]) VALUES (7, N'LINKED Q : SUB LINKED')

Upvotes: 1

Views: 1135

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Supposing that there cannot be any linked question without a root question, you can simply select all records from both tables and glue them with UNION ALL. Then we want the sort order based on the ID they have in common first. But just ordering by qid, newid() would actually sort by the ID, not randomly. So we need a random function that takes the ID and gives us a deterministic "random" value based on it: RAND(qid). But still, this would give us the same order on every execution, so we add a randomizer:

ORDER BY rand(rand(convert(int, getdate())) - qid)

It shows that SQL Server's RAND is a bit flawed when it comes to seeding (see my comment on that). This worked for me:

ORDER BY rand((datepart(mm, getdate()) * 100000) +
              (datepart(ss, getdate()) * 1000) +
               datepart(ms, getdate()) ^ qid)

As two different seeds can still lead to the same random number, we'll need to add , qid to this, so as to make sure the two linked entries get together. Moreover we add , islinked desc in order to get the question before the linked question.

The whole query:

select qid, question, solution, islinked
from
(
  select 
    id as qid, 
    question,
    solution,
    isnull(islinked, 0) as islinked, 
    id as linkkey
  from tblquest
  union all
  select 
    id as qid, 
    linkquestion as question,
    null as solution,
    0 as islinked, 
    qid as linkkey
  from tblquestlinked
) both
order by rand((datepart(mm, getdate()) * 100000) +
              (datepart(ss, getdate()) * 1000) +
               datepart(ms, getdate()) ^ qid), qid, islinked desc;

SQL fiddle: http://sqlfiddle.com/#!3/476fd/2

Upvotes: 0

James Z
James Z

Reputation: 12317

If I understood this correctly, you want the linked questions always under the main question. This is one way to do that:

select
  q.id, case when Type = 0 then Question else LinkQuestion end, Solution
from
(
  select row_number() over (order by newid()) as ORD, *
  from tblQuest q
) q
outer apply (
  select 0 as Type, 0 as QID, convert(varchar(max), NULL) as LinkQuestion
  union all
  select 1, QID, l.LinkQuestion
  from tblQuestLinked l where q.ID = l.QID
) x
order by q.ORD, x.Type, x.QID

Example in SQL Fiddle

Upvotes: 3

Related Questions