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