Select unique random rows from SQL Server table but always duplicates

Question

i am not the best with sql but i try my best to get my Problems done. I have a table "just" which is filled with Columns ( ID (PK, Identity), Char, Serv , Random ) . Now i want to select a random row from this table and insert it into the Table "f_WinGet". So far all my Procedures take this Step fine , but i always get Duplicates in the second table.

First table : 84 rows Second table: needed 35 random out of 84.

I have tried many other ways but i always get the same result. All my Procedure for random are binded to a Button i a C# Programm. All is working fine so far , but i always have some Duplicates of Rows in my Table.

 INSERT INTO f_TWinGet 
     SELECT TOP 1 Percent Char, Serv, Random
          FROM ( select distinct  Char, Serv, Random from dbo.just) as derived1 
               ORDER BY CHECKSUM(NEWID())

It would be nice , if anyone hase an Idea how i can fix my Problem. I am still trying , but all what i get are always the same result.

If you are inserting the same record it's because you keep selecting the same one. Top 1 is not a random function, it just selects the 1st row in the result set — CSharper
– CSharper, Commented Jul 19, 2014 at 19:16
You say that you want only one row, but you are actually selecting TOP 1 PERCENT of the rows. Also, if there are very few rows in the dbo.just table, than no matter what sort of randomization has been used, you will still end with some duplicates after multiple queries. — Eugene Podskal
– Eugene Podskal, Commented Jul 19, 2014 at 19:26
so far i unterstand the points what you where telling. i try to get every click on the button to select 1 random row and transfer it to the second table. so far you see the add process in the datagridview, so need to press the button 35x and always get a random row but not the same , who is already in the second table. I dont need the records from the first table so i was thinking about get rid of the choosen row in the first table so that i cant get the same row twice when i fetch the next — user3856495
– user3856495, Commented Jul 19, 2014 at 19:34
If you want to select the random non-repeating rows you have to either mark already selected rows(to exclude them from results) in db or program, or just plain generate the whole random sequence from the start(it is probably better and easier could be done on the app side). — Eugene Podskal
– Eugene Podskal, Commented Jul 19, 2014 at 19:37

Gordon Linoff · Accepted Answer · 2014-07-19 20:46:50Z

1

It you want to insert 35 rows, do it all at once:

INSERT INTO f_TWinGet(char, serv, random)
     SELECT TOP 35 Char, Serv, Random
     FROM (select distinct  Char, Serv, Random
           from dbo.just
          ) derived1 
     ORDER BY CHECKSUM(NEWID());

If you really want to do them one at a time, I would suggest using not exists:

INSERT INTO f_TWinGet(char, serv, random)
     SELECT TOP 1 Char, Serv, Random
     FROM (select distinct  Char, Serv, Random
           from dbo.just
          ) d
    WHERE not exists (select 1 from f_TWinGet f where t.char = f.char and t.serv = f.serv and t.random = f.random)
     ORDER BY CHECKSUM(NEWID());

Note that char is a reserved word, so it should be in square braces. I am leaving the names you have have them in your query.

answered Jul 19, 2014 at 20:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bruno · Accepted Answer · 2014-07-19 20:45:06Z

0

With a table as small as yours you can use something like:

INSERT INTO f_TWinGet
SELECT TOP 1 j.Char, j.Serv, j.Random
FROM dbo.just j 
LEFT JOIN f_TWinGet f
ON f.Char = j.Char
AND j.Serv = f.Serv
AND j.Random = f.Random
WHERE f.Char IS NULL
ORDER BY NEWID()

This way making sure that the values you're trying to insert is not on the final table.

edited Jul 19, 2014 at 20:45

answered Jul 19, 2014 at 20:13

bruno

2,8921 gold badge25 silver badges24 bronze badges

5 Comments

user3856495 Over a year ago

ty for helping , when i try this approch i get error message for all three columns "Meldung 209, Ebene 16, Status 1, Prozedur get_W, Zeile 10 Ambiguous column name 'Char'

CSharper Over a year ago

that column was in 2 different tables, so you have to specify which one

bruno Over a year ago

I assume that both tables have the same columns "Char", "Serv" and "Random" so you need to prefix the column name with the alias of the table you want to select from. I've updated the statement. Just change select char, serv... to select j.char, j.serv, ...

user3856495 Over a year ago

ty , i know it was a very easy problem and i am so flustered that i had so much trouble with it.

bruno Over a year ago

It happens to all of us. You are welcome and I'm glad I could help.

Collectives™ on Stack Overflow

Select unique random rows from SQL Server table but always duplicates

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related