SQL Query group shuffle

Question

I have a query like

select id, item, producer from table

The result is like this:

  id          item            producer
   1          apple            A
   2          pear             A
   3          peach            A
   4          orange           B
   5          strawberry       B
   6          melon            B

I want to shuffle this result (order by id DESC) and get something like this

item            producer
strawberry       B
pear             A
orange           B
apple            A
peach            A
melon            B

I DON'T want to display like this:

ALL A ITEM

ALL B ITEM

ALL C ITEM...

If Broken Heart's solution worked please select it as your answer using the tick under the number. — Lt_Shade, Oct 08 '13 at 08:25
No, I need something more specific. I can't shuffle the id 3 with 359. I need to select the last 50 id and shuffle them — Olivier Zoletti, Oct 08 '13 at 10:25

score 5 · Accepted Answer · answered Oct 07 '13 at 13:54

5

Use the rand function in ORDER BY like this:

select id, item, producer from table order by rand();

answered Oct 07 '13 at 13:54

Code Lღver

15,573
16
56
75

This is okay when i have 10 items. But what if I have 200 000 ? I don't want to display an item created 10 years ago – Olivier Zoletti Oct 08 '13 at 10:00
@user2761182 then use the where clause and your condition. – Code Lღver Oct 08 '13 at 12:49

score 3 · Answer 2 · edited May 23 '17 at 11:45

3

To Shuffle the selection you can use rand()

The Answer for the link below contains more information.

 SELECT id, item, producer 
 FROM table
 ORDER BY RAND()

MySQL: Shuffle a limited query result?

edited May 23 '17 at 11:45

Community

1
1

answered Oct 07 '13 at 13:52

Lt_Shade

590
1
7
18

Are you going to provide any code? This answer is 35% complete. – Kermit Oct 07 '13 at 13:53
1

@FreshPrinceOfSO Lt_Shade is not required to provide any code. - moreover, OP is encouraged to find a solution given information. – bear Oct 07 '13 at 13:54
@Shamil [How to answer](http://stackoverflow.com/questions/how-to-answer). – Kermit Oct 07 '13 at 13:55
This is okay when i have 10 items. But what if I have 200 000 ? I don't want to display an item created 10 years ago – Olivier Zoletti Oct 08 '13 at 09:59
If your entries are dated you could try use a Where clause... Or sort them by their ID's – Lt_Shade Oct 08 '13 at 13:29

bear · Answer 3 · 2013-10-07T13:57:55.123

2

 select id, item, producer from table order by rand()

Use Order BY rand() to randomly order the results

edited Oct 07 '13 at 13:57

answered Oct 07 '13 at 13:50

bear

11,364
26
77
129

1

The OP is looking for a shuffled result; not ordered. – Kermit Oct 07 '13 at 13:51
I have revised the code having review the question – bear Oct 07 '13 at 13:58

SQL Query group shuffle

3 Answers3