Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    10

    Red face Unanswered: data based on unique values in another column

    Hi all,

    A table has two columns - Dept_no. and Emp_count. The Dept_no values are unique but not the Emp_count values. I have a requirement to retireve the unique Dept_nos whose Emp_counts are unique. I tried DISTINCT but in vain.

    Is there a way to accomplish this?

    Exiting data:

    Code:
    Dept_no     Emp_count
    1                10
    2                20
    3                30
    4                30
    5                30
    6                40

    Expected result:

    Code:
    Dept_no
    1
    2
    3
    6
    Any help is highly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joe robles
    I have a requirement to retireve the unique Dept_nos whose Emp_counts are unique.
    "stupidest requirement of the day" award, but here you go anyway...
    Code:
    SELECT MIN(Dept_no) AS Dept_no
         , Emp_count
      FROM daTable
    GROUP
        BY Emp_count
    HAVING COUNT(*) = 1
    by the way, in your sample data, only departments 1, 2, and 6 should be returned

    dept 3 should ~not~ be returned, because 30 is ~not~ unique in the Emp_count column
    Last edited by r937; 07-06-09 at 16:00.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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