Solved

Increasing the chances in Order by Rand()

Posted on 2009-04-09
13
890 Views
Last Modified: 2013-12-09
Hey all,

I'm using Delphi, Firebird and Zeos to create my first game. I've got a query that I have posted below, but the one thing I would like to change (if there is any possibility and without too complex and long code) is to increase the chances of a certain result appearing in the Order by Rand().

At the moment I am using S.Nation = ''Sweden'', but what I would like is for the Order by Rand() to pick Sweden say 70% of the times and pick other nations (ie removing the S.Nation) the remaining 30%. (approx).

Also, the nation will not always be Sweden (in the end it will be determined by what nation is declared in player_nation.caption).

Any ideas, or if its even possible?
Spelare.sql.add('select First 1 S.*, L.* from Lag L join Spelare S on L.Klubb = S.Klubb');

Spelare.Sql.add('where S.Off+S.Def+S.Potential > 20 and S.Off+S.Def+S.Potential < 25 and');

Spelare.SQL.add('S.Nation = ''Sweden'' and S.Alder < 21 and NOT L.Division = ''NHL''');

spelare.sql.add('ORDER BY rand()');

Open in new window

0
Comment
Question by:MerlaP83
  • 4
  • 4
  • 2
  • +1
13 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 24110808
have a table with 70 of 100 rows containing sweden, the other containing the other options, select first 1 skip rand() tablefield from my table
0
 

Author Comment

by:MerlaP83
ID: 24111111
Unfortunately that will not work.

The table is filled with names(players) who all have different nations and attributes set and there are about 5000 records in the table. Obviously I can't change their nations.

Any other solution?
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24112366
Well i imagine this is equally an SQL question. You simply want to mix your query with another query that doesn't have Where S.Nation = 'Sweden'. You could do a union and then order by rand. Sorry I can't give you the SQL because I'm not very good at that aspect of SQL - but there are dozens of experts that are in the SQL zone.
Hope this helps.
0
 

Author Comment

by:MerlaP83
ID: 24113804
Yes, that sounds like a good idea. Just hope some SQL expert could find a solution for it :)

The chances shouldn't be 50% for Sweden and 50% for another nation, more like 70/30.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 24113962
I meant a new table
0
 

Author Comment

by:MerlaP83
ID: 24114154
I know. The thing is, sometimes the nation set isn't always Sweden.. It could be , say USA and then it should pick USA 70% of the times and other nations like Sweden, Canada, Finland, England etc 30% of the times. So creating a new table wouldnt work, if I understood you correctly.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 13

Expert Comment

by:rfwoolf
ID: 24114781
Nickupson's suggestion would technically work, but there must be a better way.
So here's my interperatation of the idea of union two SQL result sets......
in your first SQL select statement, select the top 70 of  the result set, this can be achieved with the SQL words "SELECT TOP 70"
then in your other SQL select statement you would say SELECT TOP 30.
Of course there are potential issues here. What if the first table doesn't return 70 results? What if the 2nd doesn't return 30? You may have to do a count of the records in the first table.
The good news is that the SQL used by Firebird is much more powerful than Paradox. Paradox's implementation of SQL was very basic, so what you're asking for should technically be possible. Now if only I was an expert at SQL I could do this for you.
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24115242
You could add a weighting to certain fields, in this case the smaller weighting has a better change of putting Sweden at the top
SELECT

...

ORDER BY IF(S.Nation = ''Sweden'', .3, .7) * RAND()

Open in new window

0
 

Author Comment

by:MerlaP83
ID: 24116422
Seems like that would do the trick in a very good way, oobayly. But is it supposed to work with Firebird as well?

I get the following error when trying to run the query:

SQL Error:  Dynamic SQL Error SQL error code = -104 Token unknown - line 3, column 10 IF. Error Code: -104.
0
 
LVL 15

Accepted Solution

by:
oobayly earned 250 total points
ID: 24116495
Firebird appears to use IIF instead of IF. Also, you could add the weighting field to the records instead of hard coding it into the statement.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24116563
Here's the syntax for IIF for Firebird:
http://www.firebirdsql.org/refdocs/langrefupd20-iif.html
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24125211
Hi Merla. I see you have posted a new question. Please add the SQL zone to your question. Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
First name pregmatch 11 30
Selecting specific rows 3 35
Update cached table in H2 database 6 11
simple mysql statement 3 9
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now