groupby and orderby rand() using mysql

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
manognatha
New php-forum User
New php-forum User
Posts: 10
Joined: Tue Aug 16, 2011 10:35 pm

Sun Nov 05, 2017 10:37 pm

table structure

questions_id category_id
1 1
2 1
3 2
4 2
5 3
6 3

my query

SELECT * FROM questions GROUP BY category_id ORDER BY RAND() LIMIT 0, 3

it's display any three question_id's

SET @row_number = 0;
select (@row_number := @row_number+1) AS num, id, question_name,
category_id FROM questions ORDER BY RAND() LIMIT 8;

display any 8 questions_id's

another one

SELECT * FROM questions a
INNER JOIN (SELECT category_id FROM questions GROUP BY category_id) AS b
ON a.category_id = b.category_id
ORDER BY RAND() LIMIT 0, 3

it's also display same, but i want each category_id are equal question_id

I have tried many things but have not figured it out how to get same number of rows for each group.

<tr>
<?php $main_qustions_query=$mysqli->query("SELECT * FROM questions a INNER JOIN (SELECT category_id FROM questions GROUP BY category_id) AS b ON a.category_id = b.category_id
ORDER BY RAND() LIMIT 0, 3") or die($mysqli->error.__LINE__);
$i=1; while($main_questions_row = $main_questions_query->fetch_array()) { ?>
<td class="text-center"><?php echo $i; ?></td> <td class="text-center"><?php echo $main_question_row['question_id']; ?></td>
</tr>
<?php ++$i; } ?>
Last edited by manognatha on Mon Nov 06, 2017 10:35 pm, edited 1 time in total.

User avatar
benanamen
New php-forum User
New php-forum User
Posts: 23
Joined: Mon Oct 16, 2017 1:06 pm

Mon Nov 06, 2017 2:46 pm

I don't know about anyone else, but I have no idea what you're asking. Rather than ask us about your attempted solution, why don't you explain the overall of what you are doing and trying to accomplish.
The XY Problem
The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

Post Reply