CODEWITHSUNDEEP
phpmysqlrandomdistinct
user759542
user759542

Reputation: 73

MySQL order by random but show distinct first 5 results?

Ok I've got 3 MySQL tables

Categories Companies Offers

I want someone to click onto a Category page, whereby they are shown offers from each company in a random order, so that no-one is given preference. A company can have anywhere between 1 and 10 offers and there can be any number of offers linked to a category.

This is all working fine but I want the top 5 (or more, whatever is easiest) offers to be displayed to be from unique companies. The others underneath can be in any order as long as they don't repeat those already showin in the top 5.

Not sure of the best way to go about this as it's a bit outside my ability - would it be to do 2 queries and left join them, excluding the offer ids from the first query in the second one?

Any help much appreciated!

Upvotes: 0

Views: 1838

Answers (3)

user759542
user759542

Reputation: 73

I used what nnichols suggested but tweaked (the initial query was taking 3.5 seconds to run for me for some reason) so I used this:

SELECT * 
FROM (SELECT offers.companyid, offerid, title, offer 
      FROM companies 
      INNER JOIN offers ON offers.companyid = companies.companyid 
      WHERE offers.categoryid='%s' AND offers.active = '1' 
      ORDER BY RAND()) AS t 
GROUP BY t.companyid 
ORDER BY RAND() 
LIMIT 10

Which is only taking 0.0088 to run. I then stored the offer id's being used and used the NOT IN from nnichols for the second query, it works great now

Thanks again for everyone's help :)

Upvotes: 0

user1191247
user1191247

Reputation: 12973

There must be a more efficient way of doing this but my brain is not at 100% today. How about -

SELECT
    company_offers.companyid,
    offers.offerid,
    offers.title,
    offers.offer
FROM (
    SELECT
        companies.companyid,
        (
            SELECT offers.offerid
            FROM offers
            WHERE offers.companyid = companies.companyid
            AND categoryid = '%s'
            AND active = 1
            ORDER BY RAND()
            LIMIT 1
        ) AS offerid
    FROM companies
    WHERE EXISTS (SELECT NULL FROM offers WHERE offers.companyid = companies.companyid AND offers.categoryid = '%s' AND offers.active = 1)
    ORDER BY RAND()
    LIMIT 5
) AS company_offers
INNER JOIN offers
    ON company_offers.offerid = offers.offerid

UPDATE: Added EXISTS to the inner companies SELECT

then exclude these 5 offer_ids from you main SELECT statement.

SELECT companies.companyid, offers.offerid, offers.title, offers.offer
FROM offers
INNER JOIN companies
    ON companies.companyid = offers.companyid
WHERE offers.categoryid = '%s'
AND offers.active = 1
AND offers.offerid NOT IN (comma separated list of 5 from previous query)
ORDER BY RAND()

Upvotes: 1

Mr_Chimp
Mr_Chimp

Reputation: 6917

Untested but something like this should work. Can we see your current query?

SELECT DISTINCT company, and, other, columns 
FROM table
ORDER BY RAND()
LIMIT 5

Upvotes: 1

Related Questions