locked
How to SELECT TOP 5 Department WITH TOP 1 Employee? RRS feed

  • Question

  • I would like to SELECT and result would be: The TOP 5 Departments, with the TOP 1 Employee.

    Currently, I can only produce the TOP 5 Employees in the First department:
    SELECT TOP 5 Department.*, Employee.Name as EmployeeName
    FROM Department
    INNER JOIN Employee ON Employee.Department_ID = Department.ID

    Result:
    Department1, Employee1.1
    Department1, Employee1.2
    Department1, Employee1.3
    Department1, Employee1.4
    Department1, Employee1.5

    Goal:
    Department1, Employee1.1
    Department2, Employee2.1
    Department3, Employee3.1
    Department4, Employee4.1
    Department5, Employee5.1


    Thanks!
    Tuesday, October 7, 2008 7:07 AM

Answers


  • Code Snippet


    SELECT TOP 5 D.*, EmployeeName = (

    SELECT TOP 1 Employee.Name 

    FROM Employee 

    WHERE Employee.Department_ID = D.ID

    ) FROM Department D




    Tuesday, October 7, 2008 7:50 AM
    Answerer
  • found the answer in:

    http://forums.devshed.com/database-management-46/select-top-5-from-inner-join-530577.html
    Tuesday, October 7, 2008 8:07 AM

All replies


  • Code Snippet


    SELECT TOP 5 D.*, EmployeeName = (

    SELECT TOP 1 Employee.Name 

    FROM Employee 

    WHERE Employee.Department_ID = D.ID

    ) FROM Department D




    Tuesday, October 7, 2008 7:50 AM
    Answerer
  • found the answer in:

    http://forums.devshed.com/database-management-46/select-top-5-from-inner-join-530577.html
    Tuesday, October 7, 2008 8:07 AM
  • thanks! I found a similar answer just in time. That helped a lot, thanks again. Now em just trying to figure out how to LINQ this LOL
    Tuesday, October 7, 2008 8:09 AM