CODEWITHSUNDEEP
sqlsql-server
nexus.so
nexus.so

Reputation: 35

Repeat query if no results came up

Could someone please advise on how to repeat the query if it returned no results. I am trying to generate a random person out of the DB using RAND, but only if that number was not used previously (that info is stored in the column "allready_drawn"). At this point when the query comes over the number that was drawn before, because of the second condition "is null" it does not display a result. I would need for query to re-run once again until it comes up with a number.

DECLARE @min INTEGER;
DECLARE @max INTEGER;

set @min = (select top 1 id from [dbo].[persons] where sector = 8 order by id ASC);
set @max = (select top 1 id from [dbo].[persons] where sector = 8 order by id DESC);

select 
ordial,
name_surname

from [dbo].[persons]

where id = ROUND(((@max - @min) * RAND() + @min), 0) and allready_drawn is NULL

The results (two possible outcomes):

enter image description here

Any suggestion is appreciated and I would like to thank everyone in advance.

Upvotes: 0

Views: 242

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

@gbn that's a correct solution, but it's possible it's too expensive. For very large tables with dense keys, randomly picking a key value between the min and max and re-picking until you find a match is also fair, and cheaper than sorting the whole table.

Also there's a bug in the original post, as the min and max rows will be selected only half as often as the others, as each maps to a smaller interval. To fix generate a random number from @min to @max + 1, and truncate, rather than round. That way you map the interval [N,N+1) to N, ensuring a fair chance for each N.

For this selection method, here's how to repeat until you find a match.

--drop table persons
go
create table persons(id int, ordial int, name_surname varchar(2000), sector int, allready_drawn bit)

insert into persons(id,ordial,name_surname,sector, allready_drawn) 
            values (1,1,'foo',8,null),(2,2,'foo2',8,null),(100,100,'foo100',8,null)

go
declare @min int = (select top 1 id from [dbo].[persons] where sector = 8 order by id ASC);
declare @max int = 1+ (select top 1 id from [dbo].[persons] where sector = 8 order by id DESC);


set nocount on
declare @results table(ordial int, name_surname varchar(2000))

declare @i int = 0
declare @selected bit = 0


while @selected = 0
begin
    set @i += 1

    insert into @results(ordial,name_surname)
    select 
    ordial,
    name_surname
    from [dbo].[persons]
    where id = ROUND(((@max - @min) * RAND() + @min), 0, 1) and allready_drawn is NULL

    if @@ROWCOUNT > 0
    begin
       select *, @i tries from @results
       set @selected = 1
    end
end

Upvotes: 0

gbn
gbn

Reputation: 432180

Just try this to remove the "id" filter so you only have to run it once

select TOP 1
    ordial,
    name_surname
from [dbo].[persons]
where allready_drawn is NULL
ORDER BY NEWID()

Upvotes: 1

Related Questions