MySQL - How to select random after select top?

Question

I have a query like this:

SELECT Id, Name, image, price, view FROM estore.product ORDER BY view DESC LIMIT 9

and I want select random 5 records in that query. I tried but this code doesn't work:

SELECT Id, Name, Image, Price, View FROM (
    SELECT Id, Name, Image, Price, View FROM estore.product ORDER BY View DESC LIMIT 9) 
    ORDER BY RAND() LIMIT 5

How can I do? Thanks for watching?

Side-note for editing: Use `four-space indentation` for your code instead of using `this` :) — Kevin, Jun 17 '13 at 15:15

score 4 · Accepted Answer · answered Jun 17 '13 at 15:12

4

A subquery must be named. Try:

LIMIT 9) as SubQueryAlias ORDER BY RAND() 
        ^^^^^^^^^^^^^^^^^^

answered Jun 17 '13 at 15:12

Andomar

232,371
49
380
404

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

You may want to go and read this thread Multiple rows alternative for RAND()

If your table is quite large (and it probably can end up quite large being a product table) the rand() limit is quite a slow query

edited May 23 '17 at 11:50

Community

1
1

answered Jun 17 '13 at 15:15

Dave

3,280
2
22
40

1

You can see from the query that at most 9 rows will be sorted by `rand()` – Andomar Jun 17 '13 at 15:17
Have another look at limit clause in the subquery – Andomar Jun 17 '13 at 15:20
Yes I see the 9 :) but it doesn't change it being slow / expensive as a query. – Dave Jun 17 '13 at 15:22
I just started MySQL so in this time I need my program runs and runs. – Killer Whale Jun 17 '13 at 16:07

inf3rno · Answer 3 · 2013-06-17T18:31:38.883

0

SELECT Id, Name, Image, Price, View FROM estore.product ORDER BY RAND() LIMIT 5

(Using subquery for order and limit the same selection is madness...)

edited Jun 17 '13 at 18:31

answered Jun 17 '13 at 15:22

inf3rno

24,976
11
115
197

MySQL - How to select random after select top?

3 Answers3