1

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.

8
  • 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? – Julien Vavasseur Mar 8 '16 at 13:17
  • what i have done is all there infront of you – rva Mar 8 '16 at 13:21
  • 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? – Julien Vavasseur Mar 8 '16 at 13:22
  • sorry for that....for editing so lately – rva Mar 8 '16 at 13:28
  • Can you answer the questions? It will help me help you. – Julien Vavasseur Mar 8 '16 at 13:29
0

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
1
  • 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. – Julien Vavasseur Mar 8 '16 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.