Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: How to get at max 2 employee from each department?

    Hello Sir,
    I need a SQL query in whichi want to select N number of employee from table Training_emp such that
    ----> There are at max 2 emp from any department

    MyTableStructure is:
    Training_emp:
    Emp_code
    Topic
    Session
    Month
    Inn_add
    Attend
    Date

    Emp_master:
    Emp_code
    DeptName

    now what i want is..to select say 20 employees from Training_emp table....
    suct that no more that two employeees from same departnment are selected.
    My frontend is VB and back end is MS access

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to look at the TOP predicate, specify a sort order and define a Group By
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Hello Healdem,
    Can you please give me a hint how to formulate..
    I had tried a lot using these key words, but still no solution...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT Emp_code, DeptName
    FROM Emp_master
    WHERE Emp_code IN (SELECT TOP 2 Emp_code FROM Emp_master AS topTwo WHERE DeptName = Emp_master.DeptName ORDER BY Emp_code)

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Mr. pootle flump,
    Thanks a lot. Finally i got my solution.......Thank you very much..

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •