Solved

Increasing the chances in Order by Rand()

Posted on 2009-04-09
13
901 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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
 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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