CODEWITHSUNDEEP
mysqlrandom
Samantha
Samantha

Reputation: 13

Mysql-> Group after rand()

I have the following table in Mysql

Name  Age   Group

 abel    7      A
 joe     6      A
 Rick    7      A
 Diana   5      B
 Billy   6      B
 Pat     5      B

I want to randomize the rows, but they should still remain grouped by the Group column. For exmaple i want my result to look something like this.

 Name  Age   Group

 joe     6      A
 abel    7      A
 Rick    7      A
 Billy   6      B
 Pat     5      B
 Diana   5      B

What query should i use to get this result? The entire table should be randomised and then grouped by "Group" column.

Upvotes: 1

Views: 116

Answers (4)

symcbean
symcbean

Reputation: 48387

What you describe in your question as GROUPing is more correctly described as sorting. This is a particular issue when talking about SQL databases where "GROUP" means something quite different and determines the scope of aggregation operations.

Indeed "group" is a reserved word in SQL, so although mysql and some other SQL databases can work around this, it is a poor choice as an attribute name.

SELECT *
FROM yourtable
ORDER BY `group`

Using random values also has a lot of semantic confusion. A truly random number would have a different value every time it is retrieved - which would make any sorting impossible (and databases do a lot of sorting which is normally invisible to the user). As long as the implementation uses a finite time algorithm such as quicksort that shouldn't be a problem - but a bubble sort would never finish, and a merge sort could get very confused.

There are also degrees of randomness. There are different algorithms for generating random numbers. For encryption it's critical than the random numbers be evenly distributed and completely unpredictable - often these will use hardware events (sometimes even dedicated hardware) but I don't expect you would need that. But do you want the ordering to be repeatable across invocations?

 SELECT *
 FROM yourtable
 ORDER BY `group`, RAND()

...will give different results each time.

OTOH

SELECT
FROM yourtable
ORDER BY `group`, MD5(CONCAT(age, name, `group`))

...would give the results always sorted in the same order. While

SELECT
FROM yourtable
ORDER BY `group`, MD5(CONCAT(DATE(), age, name, `group`))

...will give different results on different days.

Upvotes: 2

Sriram Jano
Sriram Jano

Reputation: 56

Try this:

SELECT * FROM table order by Group,rand()

Upvotes: 0

Strawberry
Strawberry

Reputation: 33943

DROP TABLE my_table;

CREATE TABLE my_table
(name VARCHAR(12) NOT NULL
,age INT NOT NULL
,my_group CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
('Abel',7,'A'),
('Joe',6,'A'),
('Rick',7,'A'),
('Diana',5,'B'),
('Billy',6,'B'),
('Pat',5,'B');

SELECT * FROM my_table ORDER BY my_group,RAND();
+-------+-----+----------+
| name  | age | my_group |
+-------+-----+----------+
| Joe   |   6 | A        |
| Abel  |   7 | A        |
| Rick  |   7 | A        |
| Pat   |   5 | B        |
| Diana |   5 | B        |
| Billy |   6 | B        |
+-------+-----+----------+

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12714

Do the random first then sort by column group.

select Name, Age, Group 
from (
 select *
 FROM yourtable
 order by RAND()
     ) t
order by Group

Upvotes: 0

Related Questions