Re: Selecting Distincts

Sybrand,

Thank you for your response.

However, I'm getting two problems:

If I do a
select count(*)
from emp
where (emp_id, emp_dept)
in
(select emp_id, emp_dept
 from emp
 group by emp_id, emp_dept
 having count(*) = 1
)
I'd get, 31977 rows.

If I do
select distinct empid, emp_dept
from emp
I get 34016 rows while there are 36085 in total in this table.

How do I get the 34016 distinct rows associated with emp_id, emp_dept along with the rest of the columns?

I have tried creteing a view on distinct emp_id, emp_dept and then doing a join back to the emp table using the emp_id and emp_dept columns, but that did'nt work either.

Anyway, I'm open to ideas. I'd hate to use a cursor to do this.

Thanks again.

mkb

Sybrand Bakker wrote:

> 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 - 14:27:27 CDT