Re: Selecting Distincts

Two solutions
select * from emp
where (emp_id, emp_dept)
in
(select emp_id, emp_dept
 group by emp_id, emp_dept
 having count(*) = 1
)
or more dirty and faster
select * from emp x
where not exists
(select 'x' from emp y
 where y.emp_id = x.emp_id
 and y.emp_dept = x.emp_dept
 and y.rowid <> x.rowid
)

Hth,

Sybrand Bakker, Oracle DBA

MKB wrote in message <3731C983.98FD133_at_mci.com>...
>I've got a problem that I'm trying to solve. Say I have a table as
>follows:
>emp
>-----
>emp_id
>emp_dept
>emp_name
>emp_desc
>
>How can I get all columns where distinct (emp_id and emp_dept), ie
>select *
>from emp
>where distinct (emp_id and emp_dept)
>
>Any help/insights appreciated.
>
>Thanks
>
>mkb
>
>
Received on Thu May 06 1999 - 12:16:13 CDT