How do I combine multiple SQL SELECT statements that are selecting random records? [VB6 with MS Access]

I have an SQL SELECT statement in VB6 that randomly selects 2 catchers from a baseball database:

 SqlString = "SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1, (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, BB, SO, Batting.SB, Batting.CS FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Batting.yearID > 1929 AND NOT POS = 'P' AND NOT POS = 'PI' AND NOT POS = 'OF' AND POS = 'C' ORDER BY Rnd([ndx]) DESC"

The statement ignores Pitchers (P), Pitcher batting data (Pi), and Outfielder data rows where a specific position in the outfield has not been defined (OF).

I want to turn this statement into a series of 8 statements that gets 2 random records for all player positions (except for pitchers). These positions are 'C', '1B', '2B', '3B', 'SS', 'LF', 'CF', 'RF'. I can get it to work for a single position.... but I don't know how to UNION 8 of them together.

I'm getting error messages saying "only those fields requested in the first query can be included in an ORDER BY expression' when I try to do it myself.

Anyone know how to do this?       Thanks for any assistance....
LVL 4
jazjefAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
1. take out the order by

2. do each query, in between add UNION ALL

3. add back an order by, but this now is for all the rows and t should not reference the random function because that has been done already

e.g.

SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1
union all
SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1
union all
SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1
..

order by Expr1
0
jazjefAuthor Commented:
Hi Paul...
I'm getting the following error message:
"The ORDER BY expression (rnd([ndx])) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression"

Here's what I tried to do:

SqlString = "SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1, (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, ((H - (([2B]) + ([3B]) + (
))) + ((2*[2B]) + (3*[3B]) + (4*
))) AS TB, BB, SO, Batting.SB, Batting.CS, ((H + BB + HBP) / (AB + BB + HBP + SF)) AS OBP, (TB / AB) AS SLG, (H / AB) AS BAvg, ((Fielding.PO + Fielding.A) / (Fielding.PO + Fielding.A + Fielding.E)) AS FPCT FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Batting.yearID > 1929 AND NOT POS = 'P' AND NOT POS = 'PI' AND NOT POS = 'OF' AND POS = 'C'" & _
" UNION ALL " & _
"SELECT TOP 2 ndx, Rnd([ndx]) AS Expr1, (Master.nameFirst + '_' + Master.nameLast) AS Player, (Batting.yearID & Batting.teamID) AS Team, Fielding.POS AS POS, Batting.G, AB, R, H, [2B], [3B], HR, RBI, ((H - (([2B]) + ([3B]) + (
))) + ((2*[2B]) + (3*[3B]) + (4*
))) AS TB, BB, SO, Batting.SB, Batting.CS, ((H + BB + HBP) / (AB + BB + HBP + SF)) AS OBP, (TB / AB) AS SLG, (H / AB) AS BAvg, ((Fielding.PO + Fielding.A) / (Fielding.PO + Fielding.A + Fielding.E)) AS FPCT FROM (Batting INNER JOIN Master ON Batting.playerID = Master.playerID) INNER JOIN Fielding ON Batting.playerID = Fielding.playerID WHERE Batting.yearID > 1929 AND NOT POS = 'P' AND NOT POS = 'PI' AND NOT POS = 'OF' AND POS = '1B'" & _
" ORDER BY rnd([ndx])"

The first half is for 'C' (catchers) .... the second half is for '1B' (first basemen).

I'm not sure where it is failing.
0
jazjefAuthor Commented:
If I leave the  " ORDER BY rnd([ndx])"  off, then it works and returns 1 row of each----but it always selects the same two rows of data even if I close the database and reopen it. So, it does complete the multiple select statement with the UNION ALL, but it only returns one row of each (not 2) and it's no longer random as it always selects the same two rows of data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
Mmm ok, think I know why it isn't giving random records. To successfully use TOP you need ORDER BY

try this:

(SELECT TOP 2 ndx FROM ... ORDER BY rnd(ndx) )
union all
(SELECT TOP 2 ndx FROM ... ORDER BY rnd(ndx) )
union all
(SELECT TOP 2 ndx FROM ... ORDER BY rnd(ndx) )
...


in other words treat each subquery as a unit enclosed in parentheses. This I hope will give you the wanted randomness.

But do note this won't guarantee that the same record isn't selected more than once
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
oh, the RND() function may need further input to get a different seed value for each order by. e.g.


(SELECT TOP 2 ndx FROM ... ORDER BY rnd(-(1000000*ndx)*Time()) )
union all
(SELECT TOP 2 ndx FROM ... ORDER BY rnd(-(1000000*ndx)*Time()) )
union all
(SELECT TOP 2 ndx FROM ... ORDER BY rnd(-(1000000*ndx)*Time()) )

you could also try using primes instead of the 1000000 &/or different values for each subquery
0
jazjefAuthor Commented:
OK; I am getting the full recordset every time now----16 rows of data. However, they are still not random---even after playing with the values. I've tried integers, getting rid of the '-' sign, using an entirely different number on each SELECT statement.

Here's the format that returns all 16 rows of data:
ORDER BY rnd((43*ndx)*Time())
...where '43' is any number I want to put in there; I went to Randomizer.org and generated a set of random integers and inserted a different one into each SELECT statement..... but still no randomization. Should there be a 'Randomize' call or something before using Rnd?

Any ideas?
0
jazjefAuthor Commented:
Here's my Rnd statement for my MS Access query---this occurs 8 times at the end of each statement and I UNION ALL of them together (see posts above on usage and structure):

ORDER BY rnd((-7.336*ndx)*Time())


If I change the negative on the very first rnd statement to a positive, it always returns the same exact record as the first one in the flexgrid (not random), but I DO get 16 records returned.

If I leave it as a negative, then I get random records like I want----BUT, sometimes there's 14 of them, sometimes 15 of them---but never 16 of them----and the first line of the statement (asking for 'C') always returns only 1 record.

This thing is BUGGY...  The -7.336 is an arbitrary number I chose; I've tried integers---both positive and negative; I've tried Pi; I've tried Sqr(arbitrary number) square root of the number, and nothing seems to work. MS Access's response to using a Rnd function seems to be 'random' itself.

So, to sum up, I can get my 16 records, but they are not random----or I can get random records, but not 16 of them. Arrrrrrrgggggh.

I hate SQL and MS Access.

Any help?
0
PortletPaulfreelancerCommented:
It's not SQL that's to blame here... that function is exclusively part of Access

The apparent problem is that by default the RND() function is seeded by a time value, and of course the queries execute very quickly so there isn't much difference in the time of each one.

What I was trying to indicate was using a DIFFERENT value in each of the 8 uses of that function. Have you tried that?

Why would you be getting 15 instead if 16? No idea. Can you copy the table into a new access db and attach it perhaps? (if needed just delete columns with private data)

I do not use Access these days although I now can (new laptop, new MS licenses) so I am not familiar with all its foibles. I did use Access many years ago so I do have some skills with it.
0
jazjefAuthor Commented:
I've gotten it all to work; you're right..... the time seed is so close together for each statement you have to really get some drastic value differences for the Rnd to work correctly.

Here are the ones that I'm using:
ORDER BY rnd((-7.386^11*ndx)*Time())
ORDER BY rnd((9.1426^2*ndx)*Time())
ORDER BY rnd((3.2313^2*ndx)*Time())
ORDER BY rnd((1.22^4*ndx)*Time())
ORDER BY rnd((4.22^3*ndx)*Time())
ORDER BY rnd((6.3215^2*ndx)*Time())
ORDER BY rnd((1.348^5*ndx)*Time())
ORDER BY rnd((-4.645^6*ndx)*Time())
ORDER BY rnd((5.3312^7*ndx)*Time())

I just arbitrarily created them all.

To get my 16 records I just duplicated the first statement, ran it twice using a UNION ALL, and used Top 1 in both of them. That randomly selected 2 'C' (catchers) back to back---one by each statement.
0
PortletPaulfreelancerCommented:
Great! progress has been made!
0
jazjefAuthor Commented:
Thanks for the help Paul.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.