MySQL Random Result Group By Order By

问题:

How do i get a random result on group by group_id?

its similar like this: Random order for group of rows in mysql

here is my fiddle: http://sqlfiddle.com/#!9/1c73d/3

not sure why it always gives me the same result.

CREATE TABLE job
    (`job_id` int, `group_id` int, `user_id` int, `title` varchar(50), `description` varchar(55), `type` tinyint)
;

INSERT INTO job
    (`job_id`, `group_id`, `user_id`, `title`, `description`,`type`)
VALUES
    (1, 1, 100, 'Title 1', 'Text 1', 1),
    (2, 1, 100, 'Title 2', 'Text 2', 1),
    (3, 1, 200, 'Title 3', 'Text 3', 1),
    (4, 1, 200, 'Title 4', 'Text 4', 1),
    (5, 1, 300, 'Title 5', 'Text 5', 2),
    (6, 1, 400, 'Title 6', 'Text 6', 1),
    (7, 1, 200, 'Title 7', 'Text 7', 1);

Query:

select * from job
    where type = 1
    group by group_id
    order by rand()

回答1:

Assuming you want one random record from each group under type = 1:

SELECT 
*
FROM 
(
    SELECT
        *
    FROM job
    WHERE type = 1
    ORDER BY RAND()
) AS t
GROUP BY t.group_id;

---

SQL FIDDLE DEMO

回答2:

Your query is against the sql standard because you list columns in the select list that you do not list in the group by clause, nor are subject of an aggregate functions, such as count(). MySQL allows this functionality under certain sql mode settings.

However, even if this functionality enabled MySQL has restrictions on the data chosen from the non-aggregated fields:

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

So, instead of using group by, use sorting and the limit clause to limit the output:

select * from job
    where type = 1
    order by rand()
    limit 1

Pls also note that this random selection method is very resource intensive, since MySQL has to first sort the entire resultset without using any index. There are other ways to randomly select data from a table without using order by rand().

回答3:

Try

SELECT *,COUNT(*) totalCount
FROM (SELECT * FROM job WHERE type = 1 ORDER BY RAND()) as temp
GROUP BY group_id

Without grouping

SELECT * FROM job
WHERE type = 1
ORDER BY rand()
LIMIT 1

or

SELECT * FROM job
WHERE type = 1
ORDER BY rand()

回答4:

That's because GROUP BY merge the rows with the same group_id, so your query only gives you a row as result, so, in spite of the ORDER BY RAND() the result is always the same.

If you use this, you are going to have a random result, but, without being grouped:

SELECT * 
FROM job
WHERE type = 1
ORDER BY RAND()
LIMIT 1

回答5:

The 'order by' is about all the results you get. you are grouping all the results so it returns only the 1st data as there is only single group.If you have multiple group it will return 1 st data of each group.

select * from (
SELECT * from job order by rand() ) tbl
 group by group_id ;

.