Re: Simplifying MySql queries

On 2/12/2011 12:40 PM, Andre Polykanine wrote:

Hi all, I'm using in my PHP script the following four MySql 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"); and here goes the question: is there a way to make these four in one so strictly one random question is selected from all of the four categories? Thanks!

I think you will need something like this: SELECT * FROM CandidateQuestions WHERE Category IN (1,2,3,4) GROUP BY Category ORDER BY RAND() LIMIT 4 Now, what I'm not sure about, nor have I tested, is will mysql do the ORDER BY RAND() before or after the GROUP BY. If that doesn't work, then I would try this instead: SELECT DISTINCT Category, <your other columns> FROM CandidateQuestions WHERE Category IN (1,2,3,4) ORDER BY RAND() LIMIT 4 This will probably do the distinct after the ORDER BY RAND(), but again, completely untested. YMMV Give them a whirl and let us know how it works out. Jim Lucas