Select Random records then sort them.

I want to pull the first 10 random record, but then I want them in order by customer name.

The following query pulls the random 10 records, but I can't get them in order without messing up the random selection.

SELECT tblProducts.ProductID, tblProducts.Description, tblProducts.CustomerSKU,
tblProducts.QtyPerUnit, tblProducts.Units, tblCustomers.Name,
tblInventory.QtyOnHand, tblProducts.PickLocation
FROM tblProducts
INNER JOIN tblInventory ON tblInventory.ProductID = tblProducts.ProductID
INNER JOIN tblCustomers ON tblCustomers.CustomerID = tblProducts.CustomerID
WHERE tblInventory.QtyOnHand > 0
AND tblCustomers.liveCustomer = 1
AND (tblInventory.AuditDate >= '04/02/2006' OR tblInventory.AuditDate IS NULL)
ORDER BY RAND() LIMIT 10
LVL 14
CtrlAltDlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT * FROM `table` ORDER BY RAND() LIMIT 10;
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry... just a question: are you actually using MySQL or MS SQL Server?
also, which version?

I fear, that with MySQL you need to insert your select query into a temp table, and then query from that table with the final order by
in MS SSQL, it would be easier...


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CtrlAltDlAuthor Commented:
It's MySQL 4.x and I'm programming in PHP 4.3.x.

I was thinking that I would have to put it in a temp table first, but I'm not sure my MySQL supports that or not.  It seems like I should be able to do a subquery with RAND() then I can sort them in the main query.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
MySQL 4.x -> sorry, no subqueries as needed for that.
also, LIMIT in subqueries is also not available.
0
CtrlAltDlAuthor Commented:
I was able to do it with a Temp table:

CREATE TEMPORARY TABLE tempTable
SELECT tblProducts.ProductID, tblProducts.Description, tblProducts.CustomerSKU,
tblProducts.QtyPerUnit, tblProducts.Units, tblCustomers.Name,
tblInventory.QtyOnHand, tblProducts.PickLocation
FROM tblProducts
INNER JOIN tblInventory ON tblInventory.ProductID = tblProducts.ProductID
INNER JOIN tblCustomers ON tblCustomers.CustomerID = tblProducts.CustomerID
WHERE tblInventory.QtyOnHand > 0
AND tblCustomers.liveCustomer = 1
AND (tblInventory.AuditDate >= '04/02/2006' OR tblInventory.AuditDate IS NULL)
ORDER BY RAND()
LIMIT 18;
SELECT * FROM tempTable ORDER BY tempTable.Name;
DROP TEMPORARY TABLE tempTable;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.