Friday, 23 August 2013

Random selection of a percent of entries from a table

Random selection of a percent of entries from a table

I'm try to get a random selection of 30% of tickets in a bin. The below
code works for the 30%, but when I add "ORDER BY RND(INT(NOW*ID)-NOW*ID)"
to the end, I get an error.
Works:
$result1 = "SELECT MainBin.Assignee, Count(MainBin.IncidentNumber) * .3 AS
CountOfIncidentNumber FROM MainBin GROUP BY MainBin.Assignee";
$rs1=odbc_exec($conn,$result1);
while($row = odbc_fetch_array($rs1))
{
$Assignee = "". $row['Assignee'];
$CountOfIncidentNumber = ceil($row["CountOfIncidentNumber"]);
$result2 = "INSERT INTO TicketBin (IncidentNumber)
SELECT TOP $CountOfIncidentNumber IncidentNumber FROM MainBin
WHERE Assignee = '$Assignee'";
$rs2=odbc_exec($conn,$result2);
}
Does Not Work:
$result1 = "SELECT MainBin.Assignee, Count(MainBin.IncidentNumber) * .3
AS CountOfIncidentNumber FROM MainBin GROUP BY MainBin.Assignee ORDER BY
RND(INT(NOW*ID)-NOW*ID)";
$rs1=odbc_exec($conn,$result1);
I have and AutoNumber field named ID and I've used this before, just not
with the grouping. Any help is appreciated!

No comments:

Post a Comment