Simplify 4 queries into 1

Hi all,
Hope this question is appropriate here :-).
I've got 4 queries:

$q1=3Dmysql_query("SELECT * FROM `CandidateQuestions` WHERE
`Category`=3D'1' ORDER BY RAND() LIMIT 1");
$q2=3Dmysql_query("SELECT * FROM `CandidateQuestions` WHERE
`Category`=3D'2' ORDER BY RAND() LIMIT 1");
$q3=3Dmysql_query("SELECT * FROM `CandidateQuestions` WHERE
`Category`=3D'3' ORDER BY RAND() LIMIT 1");
$q4=3Dmysql_query("SELECT * FROM `CandidateQuestions` WHERE
`Category`=3D'4' ORDER BY RAND() LIMIT 1");

What I need is to select exactly 4 questions so that each of them
would be randomly selected from each category.
Note: I don't need 4 *any* questions, I need 1 question from the 1st
category, 1 question from the 2nd category etc.
Is there a way to make one query for this?
If I do
"SELECT * FROM `CandidateQuestions` WHERE `Category` IN (1,2,3,4)
GROUP BY `Category` LIMIT 4",
I think i would get 4 random questions, wouldn't I?
Thanks in advance!

=20

--=20
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Simplify 4 queries into 1

At 05:39 PM 2/13/2011, Andre Polykanine wrote:
>Hi all,
>Hope this question is appropriate here :-).
>I've got 4 queries:
>
>$q1=mysql_query("SELECT * FROM `CandidateQuestions` WHERE
>`Category`='1' ORDER BY RAND() LIMIT 1");
>$q2=mysql_query("SELECT * FROM `CandidateQuestions` WHERE
>`Category`='2' ORDER BY RAND() LIMIT 1");
>$q3=mysql_query("SELECT * FROM `CandidateQuestions` WHERE
>`Category`='3' ORDER BY RAND() LIMIT 1");
>$q4=mysql_query("SELECT * FROM `CandidateQuestions` WHERE
>`Category`='4' ORDER BY RAND() LIMIT 1");
>
>What I need is to select exactly 4 questions so that each of them
>would be randomly selected from each category.
>Note: I don't need 4 *any* questions, I need 1 question from the 1st
>category, 1 question from the 2nd category etc.
>Is there a way to make one query for this?
>If I do
>"SELECT * FROM `CandidateQuestions` WHERE `Category` IN (1,2,3,4)
>GROUP BY `Category` LIMIT 4",
>I think i would get 4 random questions, wouldn't I?

Correct.

You could use:

SELECT * FROM `CandidateQuestions` WHERE
`Category`='1' ORDER BY RAND() LIMIT 1
union SELECT * FROM `CandidateQuestions` WHERE
`Category`='2' ORDER BY RAND() LIMIT 1
union SELECT * FROM `CandidateQuestions` WHERE
`Category`='3' ORDER BY RAND() LIMIT 1
union SELECT * FROM `CandidateQuestions` WHERE
`Category`='4' ORDER BY RAND() LIMIT 1

and this returns 4 rows, with 1 random record from each category.

Mike


>Thanks in advance!
>
>
>
>--
>With best regards from Ukraine,
>Andre
>Skype: Francophile
>Twitter: http://twitter.com/m_elensule
>Facebook: http://facebook.com/menelion
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org