How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

Question

EXAMPLE

Table Dept:

 dept_id dept_name dept_status
---------------------------------------
 1 Sales 1
 2 Develpment 1
 3 HR 1
 4 QA 0

Table Employee:

 emp_id emp_dept_id emp_name emp_sal emp_status
 -------------------------------------------------------
 1 1 A 10 1 
 2 2 B 20 0
 3 1 C 30 1
 4 4 D 40 1
 5 1 E 50 0
 6 3 F 60 1
 7 4 G 70 1
 8 2 H 80 1
 9 2 I 90 1
 10 3 J 100 1
 11 4 K 110 1
 12 3 L 120 0
 13 2 M 130 1
 14 1 N 140 1
 15 4 O 150 0
 16 3 P 160 1

Table JoiningDate

 ej_id ej_emp_id ej_joining date
 ------------------------------------------
 1 1 10/05/1997
 2 2 14/05/1999
 3 3 18/05/2001
 4 4 22/05/2003
 5 5 26/05/2005
 6 6 30/05/2007
 7 7 03/06/2009
 8 8 07/06/2011
 9 9 11/06/2013
 10 10 15/06/2015
 11 11 19/06/2004
 12 12 23/06/2006
 13 13 27/06/2002
 14 14 01/07/2011
 15 15 05/07/2000
 16 16 09/07/2004

My Query is:

 SELECT * FROM `Dept` DT 
 JOIN `Employee` EMP ON DT.`dept_id` = EMP.`emp_dept_id`
 JOIN `JoiningDate` EJ ON EJ.`ej_emp_id` = EMP.`emp_id`
 WHERE
 DT.`dept_status` = '1' AND EMP.`emp_status` = '1' ORDER BY Dt.`dept_id`, RAND()

I want result for random two employees from each department.

• Julien Vavasseur: It is nice of you to change the question and add a new table... Can you add a sample of such a ramdom output and the column you need?
• rva: what i have done is all there infront of you
• Julien Vavasseur: You changed the quesiton and added another table and a different query 3 hours later. What columns are really needed? You probably don''t need * ? Have you try the answer with your original question? Have you try to use and adapt it to your needs?
• rva: sorry for that....for editing so lately
• Julien Vavasseur: Can you answer the questions? It will help me help you.

Answer 1

Answer to the new question:

SELECT `dept_name`
 , `emp_name`
 , `emp_sal`
 , `ej_joining date`
FROM (
 SELECT emp.`dept_name`, emp.`emp_name`, emp.`emp_sal`, emp.`ej_joining date`
 , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
 , @dept_id := `emp_dept_id` as `dept_id`
 FROM (
 SELECT DT.`dept_name` 
 , EMP.`emp_name`, EMP.`emp_sal`, EMP.`emp_dept_id`
 , EJ.`ej_joining date`
 FROM `Dept` DT 
 INNER JOIN `Employee` EMP ON DT.`dept_id` = EMP.`emp_dept_id`
 INNER JOIN `JoiningDate` EJ ON EJ.`ej_emp_id` = EMP.`emp_id`
 WHERE DT.`dept_status` = '1' AND EMP.`emp_status` = '1' 
 ORDER BY Dt.`dept_id`, RAND()
 ) emp
 CROSS JOIN (
 SELECT @dept_id := 0 as dept_id, @num := 0 as num
 ) v
) num 
WHERE num.num <= 2
ORDER BY `dept_name`, num.num
;

SQL Fiddle

---

This answers the original question

---

This first query will randomly order rows by dept_id. Each row in each group of dept_id is given a incremental number (num) from 1 to n.

Random order query:

SELECT `emp_name`, `emp_sal`
 , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
 , @dept_id := `emp_dept_id` as `emp_dept_id`
FROM (
 SELECT `emp_name`, `emp_sal`, `emp_dept_id`
 FROM `Employee`
 ORDER BY emp_dept_id, RAND()
) emp
CROSS JOIN (
 SELECT @dept_id := 0 as dept_id, @num := 0 as num, @numx := 0 as numx
) v

The Dept table can then be joined to this query. A filter on num value being 1 or 2 will give the first 2 rows of each dept_id. SQL Fiddle The order changes for each execution and the first 2 rows change as well.

Main query:

SELECT DT.`dept_name`, num.`emp_name`, num.`emp_sal`
FROM `DEPT` DT 
INNER JOIN (
 SELECT `emp_name`, `emp_sal`
 , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
 , @dept_id := `emp_dept_id` as `emp_dept_id`
 FROM (
 SELECT `emp_name`, `emp_sal`, `emp_dept_id`
 FROM `Employee`
 ORDER BY emp_dept_id, RAND()
 ) emp
 CROSS JOIN (
 SELECT @dept_id := 0 as dept_id, @num := 0 as num
 ) v
) num 
 ON DT.`dept_id` = num.`emp_dept_id`
WHERE num.num <= 2
ORDER BY DT.`dept_name`, num.num

• Julien Vavasseur: the 2 queries are quite similar. the inner query order and number your data. then it only keep number 1 and 2. you only need to adap it to your real data.