Link to home
Start Free TrialLog in
Avatar of MerlaP83
MerlaP83

asked on

Random query problem - should not pick the same record more than once [Firebird]

Hey all,

I have a query using Firebird where it randomly picks one record; however, I am looping this query and therefore the same record often shows up more than one time. How can I avoid this?

Should I collect the records somewhere and then go for something "and NOT" ?
for x := 1 to (strtoint(antalskadadespelare.caption)) do begin    (will loop between 1-10 times)
 
s := Random(3);
if s = 0 then
begin
Lag.close;
Lag.sql.Clear;
Lag.sql.add('select First 1 * from Lag where intspelare = 0 and antalvarv > 0 and MV < 2 and NOT Division = ''AHL'' order by rand()');
Lag.Open;
if not Lag.IsEmpty then
begin
Lag.First;
intklubb.caption := Lag['Klubb'];
intrykte.caption := Lag['Rykte'];
intland.caption := Lag['Nation'];
intekonomi.caption := Lag['Ekonomi'];
intposition.caption := 'M'
end
end
else if s = 1 then
//etc..

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nick Upson
Nick Upson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MerlaP83
MerlaP83

ASKER

Thanks for your reply.

Unfortunately that didn't work either, basically the same result. Any other solution? Should I add another field or something? Cause I really need to make it work somehow (without being too slow).
what values for the outout of rand() do you get? what verison of firebird are you using
What do you mean with outout of rand()? The table contains about 30 fields in which I need to get via the random query. I am using v2.1
when you run the query with rand in the select list what output do you get
Well, often its correct. But occasionally it returns the same result when the query is looped 3-4 times.
I suspect you are using firebird 1.5, where this is a known issue. rand is initiallised from the clock and if running quickly enough you get the same value
Actually I'm using Firebird 2.1 :/

Is there any other solution or problem you could find with the code? Should something be changed in the Delphi code?
was this a 2.1 install or did you upgrade from a 1.5 install
This was a clean 2.1 install. Not been working with Firebird before.