Solved Help with a union query

[swingline]

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.

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]

Check your ((parenthesis)

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

HTH

 

[swingline]

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.

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]

I used function to generate the random number.
Topic1 and Topic2 will both have 5 records.

 

[Guus2005]

Try this.

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

 

[swingline]

Try this.

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

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

 

[Guus2005]

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