-1

Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries:

SELECT *
FROM(
        SELECT deal_page.id,
            deal_page.merchant_id,
            deal_page.category_id,
            deal_page.emirate_id,
            deal_page.title,
            deal_page.description,
            deal_page.emirate,
            deal_page.lat,
            deal_page.lng,
            deal_page.address,
            deal_page.thumbnail_name,
            deal_page.price,
            deal_page.created_at,
            AVG(ratings.ratings) AS rating
        FROM deal_page
            LEFT JOIN ratings ON deal_page.id = ratings.deal_id
        GROUP BY deal_page.id,
            deal_page.merchant_id,
            deal_page.category_id,
            deal_page.emirate_id,
            deal_page.title,
            deal_page.description,
            deal_page.emirate,
            deal_page.lat,
            deal_page.lng,
            deal_page.address,
            deal_page.thumbnail_name,
            deal_page.price,
            deal_page.created_at
        ORDER BY deal_page.lat ASC
        LIMIT 2
    ) t1 ORDER BY RAND()
UNION DISTINCT
SELECT *
FROM(
        SELECT deal_page.id,
            deal_page.merchant_id,
            deal_page.category_id,
            deal_page.emirate_id,
            deal_page.title,
            deal_page.description,
            deal_page.emirate,
            deal_page.lat,
            deal_page.lng,
            deal_page.address,
            deal_page.thumbnail_name,
            deal_page.price,
            deal_page.created_at,
            AVG(ratings.ratings) AS rating
        FROM deal_page
            LEFT JOIN ratings ON deal_page.id = ratings.deal_id
        GROUP BY deal_page.id,
            deal_page.merchant_id,
            deal_page.category_id,
            deal_page.emirate_id,
            deal_page.title,
            deal_page.description,
            deal_page.emirate,
            deal_page.lat,
            deal_page.lng,
            deal_page.address,
            deal_page.thumbnail_name,
            deal_page.price,
            deal_page.created_at
        ORDER BY deal_page.lat ASC
    ) t2

Am not sure where I got it wrong. Without adding "ORDER BY RAND()" my query runs well but I need the first query to randomize. I need a guide on how to come about this.

6
  • Put ORDER BY RAND() after the last subquery (after ) t2). And remove ORDER BY clause from subquery t2 - it makes no sense without LIMIT clause. – Akina Aug 1 '20 at 18:43
  • Adding ORDER BY RAND() after the last subquery will randomize the final query. I want the first query to be randomized before the second query. Any help with that? – yodeveloper Aug 1 '20 at 18:47
  • 1
    Are you using SQL Server or MySQL? – Aaron Bertrand Aug 2 '20 at 0:08
  • Please don't cross post to multiple sites. stackoverflow.com/q/63199382/3404097 – philipxy Aug 2 '20 at 2:26
  • 1
    What do you want? 2 random rows from the first query plus 1 row from the second, but only if different? So you end up with 2 or 3 rows, in no particular order? Please point out what, if anything, is different in the second SELECT. – Rick James Aug 2 '20 at 3:11
0

You can simoly put around the Select ... ORDER BY RAND() in parenthesis, so that MysSQL knows how to process it

(SELECT *
FROM
( 
    SELECT hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at,
        AVG(hobber_ratings.ratings) AS ratings
    FROM hobber_deal_page
        LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
    GROUP BY hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at
    ORDER BY hobber_deal_page.lat ASC
    
    LIMIT 2
) t1 ORDER BY RAND() LIMIT 18446744073709551615)
UNION DISTINCT
(SELECT *
FROM(
    SELECT hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at,
        AVG(hobber_ratings.ratings) AS ratings
    FROM hobber_deal_page
        LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
    GROUP BY hobber_deal_page.id,
        hobber_deal_page.merchant_id,
       hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
      hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at
    ORDER BY hobber_deal_page.lat ASC
) t2)

Basically something like this, but as i mentioned ion the comment without testd data, it is not clear, if this is he solution

(SELECT *
FROM
( 
    SELECT hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at,
        AVG(hobber_ratings.ratings) AS ratings
    FROM hobber_deal_page
        LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
    GROUP BY hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at
    ORDER BY hobber_deal_page.lat ASC        
    LIMIT 2
) t1 ORDER BY RAND() LIMIT 18446744073709551615)
UNION DISTINCT
(SELECT *
FROM(
    SELECT hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at,
        AVG(hobber_ratings.ratings) AS ratings
    FROM hobber_deal_page
        LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
        WHERE  hobber_deal_page.id NOT IN (SELECT hobber_deal_page.id
    FROM hobber_deal_page
        LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
    GROUP BY hobber_deal_page.id,
        hobber_deal_page.merchant_id,
        hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
        hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at
    ORDER BY hobber_deal_page.lat ASC        
    LIMIT 2)
    GROUP BY hobber_deal_page.id,
        hobber_deal_page.merchant_id,
       hobber_deal_page.category_id,
        hobber_deal_page.emirate_id,
      hobber_deal_page.title,
        hobber_deal_page.description,
        hobber_deal_page.emirate,
        hobber_deal_page.lat,
        hobber_deal_page.lng,
        hobber_deal_page.address,
        hobber_deal_page.thumbnail_name,
        hobber_deal_page.price,
        hobber_deal_page.created_at
    ORDER BY hobber_deal_page.lat ASC
) t2)
15
  • I try to run the above query and it still didn't randomize. I want the LIMIT 2 to randomize whenever the query is initialized. – yodeveloper Aug 1 '20 at 19:37
  • the first select 2 rows with the specified order, than the 2 get a random but with two rows only you must have luck to find another order: run this repeated and see of the order changes – nbk Aug 1 '20 at 19:43
  • I increased the limit to 5, and I ran it repeatedly but still no change. It is not randomizing. What should I do to improve the query? – yodeveloper Aug 1 '20 at 20:48
  • databass are by nature unsorted, so the optumizer eliminates the ORDER By. Try the updated query, that forces the order – nbk Aug 1 '20 at 20:54
  • 1
    The ORDER BY , even with the big limit will not survive the UNION. – Rick James Aug 2 '20 at 3:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.