• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

Increasing the chances in Order by Rand()

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
MerlaP83
Asked:
MerlaP83
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Nick UpsonPrincipal Operations EngineerCommented:
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
 
MerlaP83Author Commented:
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
 
rfwoolfCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
MerlaP83Author Commented:
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
 
Nick UpsonPrincipal Operations EngineerCommented:
I meant a new table
0
 
MerlaP83Author Commented:
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
 
rfwoolfCommented:
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
 
oobaylyCommented:
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
 
MerlaP83Author Commented:
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
 
oobaylyCommented:
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
 
rfwoolfCommented:
Here's the syntax for IIF for Firebird:
http://www.firebirdsql.org/refdocs/langrefupd20-iif.html
0
 
rfwoolfCommented:
Hi Merla. I see you have posted a new question. Please add the SQL zone to your question. Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now