data based on unique values in another column

joe robles · #1 (**permalink**) 07-06-09, 14:42

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.

r937 · #2 (**permalink**) 07-06-09, 14:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech