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.

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