When you group by (emp_id, emp_dept) you will get one row for each distinct pair of (emp_id, emp_dept), your 34016 rows. The Having Count(*) = 1 clause limits rows returned to those records which have a unique (emp_id, emp_dept) combination (i.e., only one record with that combination) You must have 2069 rows which represent duplicates of a (emp_id, emp_dept) combination. (36085 - 34016).
However, you lose both those 2069 records as well as however many records in your distinct set match them because of the IN( ) function of your WHERE clause. (I count 2039 records from your distinct set which are being dropped here)
So, you are getting what you asked for if you think about it. You cannot have a query which returns all columns from those rows which have distinct (emp_id, emp_dept) values and still expect one and only one row to return for each row which does not have distinct (emp_id, emp_dept) values. If you still want one row for those records, you will have to group by those columns and decide which aggregate function delivers the values you want for the other columns in those rows.
Hope this helps
CSL
MKB <mohammed.bhatti_at_mci.com> wrote in article <3731ED9D.50E7B82_at_mci.com>...
> 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.
Received on Fri May 07 1999 - 01:42:34 CDT
