Solved Help with a union query (1 Viewer)

swingline

Member
Local time
Today, 12:21
Joined
Feb 18, 2020
Messages
51
I am trying to get random rows from the same table using a union query. If I run the first half of the query by itself self I get random rows on refresh but once I add my union for another category they are ordered by RandomValue but the query no longer pulls random rows. Any help is greatly appreciated.

Code:
SELECT Top 10 *

FROM   (SELECT *,

           Rnd(ID) AS RandomValue

        FROM   MyTable)

WHERE (((Question.CAT) IN ("topic1") AND ((Questions.Position)="Manager")

ORDER  BY RandomValue

UNION ALL
SELECT Top 4 *

FROM   (SELECT *,

           Rnd(ID) AS RandomValue

        FROM   MyTable)

WHERE (((Question.CAT) IN ("topic2") AND ((Questions.Position)="Manager")

ORDER  BY RandomValue
 

Guus2005

AWF VIP
Local time
Today, 10:21
Joined
Jun 26, 2007
Messages
2,645
Check your ((parenthesis)

I put your query in Notepad++. Which shows corresponding parenthesis. It shows that there are a few of those missing.

HTH:D
 

swingline

Member
Local time
Today, 12:21
Joined
Feb 18, 2020
Messages
51
I guess I had a copy-paste error in the original post. Below is corrected. I have hundreds of questions for each CAT but it keeps using the same ones if I union all query multiple topics together. Im looking for any work around.

Code:
SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic1")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue

UNION ALL
SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic2")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue

UNION ALL
SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic3"))
ORDER BY RandomValue

UNION ALL SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic4"))
ORDER BY RandomValue;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:21
Joined
May 7, 2009
Messages
19,169
I used function to generate the random number.
Topic1 and Topic2 will both have 5 records.
 

Attachments

  • Random5.zip
    29.2 KB · Views: 317

Guus2005

AWF VIP
Local time
Today, 10:21
Joined
Jun 26, 2007
Messages
2,645
Try this.
Code:
SELECT TOP 10 * FROM
((SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic1")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue)

UNION ALL
(SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic2")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue)

UNION ALL
(SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic3"))
ORDER BY RandomValue)

UNION ALL 
(SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic4"))
ORDER BY RandomValue))

If the query is slow, create a temporary table/query with the added randomised value.
Then use '=' in your where statements instead of 'IN'.

HTH:D
 

swingline

Member
Local time
Today, 12:21
Joined
Feb 18, 2020
Messages
51
Try this.
Code:
SELECT TOP 10 * FROM
((SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic1")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue)

UNION ALL
(SELECT TOP 3 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE (((Questions.CAT) In ("Topic2")) AND ((Questions.Position)="Manager"))
ORDER BY RandomValue)

UNION ALL
(SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic3"))
ORDER BY RandomValue)

UNION ALL
(SELECT TOP 2 *
FROM (SELECT *,Rnd(ID) AS RandomValue
FROM Questions)
WHERE ((Questions.CAT) In ("Topic4"))
ORDER BY RandomValue))

If the query is slow, create a temporary table/query with the added randomized value.
Then use '=' in your where statements instead of 'IN'.

HTH:D

This gave me a syntax error but works if I drop off the first ( and last ) think this is a subquery?
 

Guus2005

AWF VIP
Local time
Today, 10:21
Joined
Jun 26, 2007
Messages
2,645
Yes, it is. You use them all the time.
I know it is not nice to look at. But it gets the job done.
You might want to chop it into smalller pieces so it is better to debug and you still know what happens when you look at it a year from now...
 

Users who are viewing this thread

Top Bottom