?
Solved

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

Posted on 2009-04-19
10
Medium Priority
?
433 Views
Last Modified: 2013-12-09
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

0
Comment
Question by:MerlaP83
  • 5
  • 5
10 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
ID: 24179800
'select First 1 * from Lag where intspelare = 0 and antalvarv > 0 and MV < 2 and NOT Division = ''AHL'' order by rand()
I don't think this will work as the rand will become the column number
 
try this
 
'select First 1 rand(), L.* from Lag L where intspelare = 0 and antalvarv > 0 and MV < 2 and NOT Division = ''AHL'' order by 1
0
 

Author Comment

by:MerlaP83
ID: 24179841
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).
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24179904
what values for the outout of rand() do you get? what verison of firebird are you using
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:MerlaP83
ID: 24184668
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
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24184763
when you run the query with rand in the select list what output do you get
0
 

Author Comment

by:MerlaP83
ID: 24209095
Well, often its correct. But occasionally it returns the same result when the query is looped 3-4 times.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24212201
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
0
 

Author Comment

by:MerlaP83
ID: 24212576
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?
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24215799
was this a 2.1 install or did you upgrade from a 1.5 install
0
 

Author Comment

by:MerlaP83
ID: 24216345
This was a clean 2.1 install. Not been working with Firebird before.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question