In Sql Result not to get repeated Id's or result

Question

Assume that i have a table below

S.no   Question
 1      XXX 
 2      XXX
 3      XXX 
 4      XXX
 5      XXX
 6      XXX
 7      XXX
 8      XXX
 9      XXX
 10     XXX

and i want to get a random new result from the table and i dont want repeated id's and i have tried

SET @Q_ID=(SELECT Top 1 S.No from Table  order by NEWID())

but im getting sometimes repeated results , can anyone help me???

Is it a requirement to return **one** random row at a time? There are simple ways to return a unique result set. — Mackan, May 05 '15 at 06:35
Not 100% sure, but I assume SELECT @@IDENTITY AS 'Identity'; is what you are looking for. — NickD, May 05 '15 at 06:43

Paolo Costa · Accepted Answer · 2015-05-05T06:56:37.267

2

You have to store your already returned result somewhere (for eaxample in another table) and then add a LEFT JOIN to check the value does not exist.

Here's the table

CREATE table AlreadyReturned
(
  Id int,
  UserName varchar(200)
)

And here's the code:

DECLARE @Q_ID int

// @Username will be passed as a paremeter of course, here
// it is set for simplicity
DECLARE @Username varchar(200)
SET @Username = 'test' 

WHILE @Q_ID IS NULL
 BEGIN
    SET @Q_ID=(
          SELECT Top 1 MyTable.[No] 
          FROM MyTable LEFT OUTER JOIN AlreadyReturned 
              ON MyTable.[No] = AlreadyReturned.Id AND AlreadyReturned.UserName = @UserName 
          WHERE AlreadyReturned.Id IS NULL order by NEWID())

    IF @Q_ID IS NULL
       DELETE FROM AlreadyReturned WHERE UserName = @Username
END 

INSERT INTO AlreadyReturned VALUES (@Q_ID, @Username)

SELECT @Q_ID

Take care this code is like a "Time Bomb", as it works until the AlreadyReturned table does not contain all the values that are present in the MyTable table

edited May 05 '15 at 06:56

answered May 05 '15 at 06:30

Paolo Costa

1,989
1
12
15

yeah you are right but where i want to clear the AlreadyReturned tables for new users who are attending the questions – dazzling kumar May 05 '15 at 06:33
Ok, you have to add a UserId field to the table, I'll update the answer – Paolo Costa May 05 '15 at 06:34
the user will attend questions as many times he want, is it right to store his username every time he attends??? – dazzling kumar May 05 '15 at 06:42
Yes, it's the only way to check whether he already obtained that value in the past. – Paolo Costa May 05 '15 at 06:44
thanks for ur suggestions and im gona try it :) and also i think i have to write a trigger to clear the table once in a month or week – dazzling kumar May 05 '15 at 06:48
Maybe you can clear it when you don't have any new result, would you like to have the code for this too :-) – Paolo Costa May 05 '15 at 06:52
1

@dazzlingkumar Obviously it would be better to store the user **Id**, but we don't know your db design. If you have user id's, use them instead – Mackan May 05 '15 at 06:53
yeah it would be better to have it @PaoloCosta :) – dazzling kumar May 05 '15 at 06:54
1

I delete the comment with the email – Paolo Costa May 05 '15 at 07:07

score 1 · Answer 2 · edited May 23 '17 at 11:51

You will have to store whatever random question id was selected and exclude from selecting next time . Create a table QuestionsSelectedTable which stores already selected questions.

SET @Q_ID=(SELECT Top 1 S.No from Table  WHERE [S.No] NOT IN (SELECT Qid FROM QuestionsSelectedTable ) order by NEWID())
INSERT INTO QuestionsSelectedTable(Qid) VALUES(@QID)

Doing ORDER BY NEWID() selects randomly but is not guaranteed to return different result everytime as there is no dependency to the result which was generated before. Smaller the no of records in table , more are the chances of repeated results.

See here how NEWID() works

yes ur's also similar to Paolo Costa but where i want to clear the AlreadyReturned tables for new users who are attending the questions — dazzling kumar, May 05 '15 at 06:38

In Sql Result not to get repeated Id's or result

2 Answers2