Re: Selecting Distincts

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Thu, 06 May 1999 13:13:47 -0700
Message-ID: <3731F7FA.D21514B9@earthlink.net>

How about this:

select     A.emp_id
,          A.emp_dept
,          B.emp_name
,          B.emp_desc
from        (select     distinct   emp_id
            ,                      emp_dept
            from  emp)                             A
,           emp                                    B
where    A.emp_id    = B.emp_id
and      A.emp_dept  = B.emp_dept

/

Good Luck!

+--------------------------------------------------------------------------+

| Vitaliy Mogilevskiy
| Senior Consultant
| CORE Technology Group, Inc.
| E-mail:    vit100gain_at_earthlink.net
| Fax :      (707) 516-2163
| Web Page:  http://home.earthlink.net/~vit100gain/index.html

|            *** Free DBA Script Library at my Web Page ***
+--------------------------------------------------------------------------+

MKB wrote:

> 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?

> Received on Thu May 06 1999 - 15:13:47 CDT