MySQL select random rows with order by

Question

I want to retrieve random rows from table but this rows must be order in category.

select category, 
   (select order_number 
   from orders 
   where order_number in (123,125,128,129,256,263,966,258,264,159,786) 
   order by rand()) 
from orders 
order by category

This is the query I tried. But that retrieves whole data in table.

Worked query ;

SELECT category,order_number FROM (
    SELECT category,order_number 
    from orders 
    where order_number in (`$order_numbers_variable`) 
    order by rand()
) order by category

It's about 111 row. I mean there are 111 rows. Order_number field is unique. Category field is repeated. So I want to order by category in the same time select random order_number. That is like order in grouped field. — Yasin Yörük
– Yasin Yörük, Commented Jan 14, 2013 at 11:14

Jirilmon · Accepted Answer · 2013-01-14 10:33:27Z

1

I assume the requirement is: Retrieve 'N' random rows from a table sorted by 'category'.

Lets assume N is 10. If you want to change the number of rows, then change it in the LIMIT clause.

SELECT * FROM (
    SELECT category from orders ORDER BY rand() ASC  LIMIT 10
) AS innerResult 
ORDER BY innerResult.category

answered Jan 14, 2013 at 10:33

Jirilmon

1,9441 gold badge12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jirilmon Over a year ago

@Yasin Yörük Please let know if this answer helps.

Yasin Yörük Over a year ago

Thanks friend. Your query is help me to solve my problem. I changed something little in the query.

Collectives™ on Stack Overflow

MySQL select random rows with order by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related