Shuffle records and sequential records in SQL Server

advertisements

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

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):

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')


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