CODEWITHSUNDEEP
mysqlsql
maugch
maugch

Reputation: 1318

random order for grouped and limited query

I have a query like 

SELECT name,count(*) cnt group by b order by cnt desc limit 10;

So I will have a list of items like

Name    Count

test     6
test2    4
test6    1
test23   1
test4    1

The problem is that there are 1000s results with cnt=1 and I would like to show them randomly, otherwise I'd always show more or less the same for every query. What I would like is a random order on the rows with same cnt value. Is it doable?

Upvotes: 2

Views: 83

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use RAND() in order by after your count

SELECT name,count(*) cnt from t
 group by b order by cnt desc ,RAND()
 limit 10;

So first rows will be ordered by cnt in descending order then for the same value of count (cnt) rows will be ordered randomly

Sample demo with provided dataset

Upvotes: 4

Related Questions