There is a table with the fields id, name, hit .
It has 100 entries.
5 of them - hit = 1

How to display correctly in this sorting: In the first ten there should always be 5 products with hit = 1 , shuffled randomly. Next, the rest of the goods Random

  • order by hit=1 desc, rand() This is if you don’t need to select only 5 out of more than 5 hit - Mike

2 answers 2

 select * from ( select A.*,@nohit:=if(hit<>1,@nohit+1,0),@cnt:=if(@nohit=1,1,@cnt+1) num from ( select * from table order by hit=1 desc, rand() ) A, (select @cnt:=0,@nohit:=0) B ) A order by num<=5 desc, rand()

Here, first all hit = 1 are put at the beginning and all entries are sorted in random order. Further, in this set, hit = 1 and hit <> 1 are numbered consecutively, separately, starting with 1. That is, hit = 1 entries with numbers 1,2,3, ... and exactly hit <<1 1,2,3, ... We sort this set again, this time there are entries with numbers less than 5 at the beginning (there are 10 of them, 5 hits each and not hit ones), and then everything else.

     SELECT * FROM `om_table` WHERE `hit` = 1 ORDER BY rand() LIMIT 5 UNION SELECT * FROM `om_table` WHERE `hit` <> 1 ORDER BY rand() LIMIT 5
    • And you check requests before the answer to write? MySQL says this on ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY - Mike
    • Did not check, give your version of MySQL and check in it. - Makarenko_I_V
    • 3
      It will be on any other version of MySQL and on any other RDBMS the same will be an error, because according to the SQL standards, ORDER BY is applied after UNION has been completed, so union subqueries must be made. BUT SQL (by standard) does not guarantee that the records of the first part of the union will be necessarily before the records of the second part, although MySQL of the current versions does put records in the order of the union - Mike