selecting unique row from a table- ORACLE

I have a table employee that has employee’s benefit data. I have a field in the table called isenrolled if field is 1 that means employee has enrolled for benefit and if field is 0 that means not enrolled. My problem is i have multiple recs of a employee, that means Scott has two entries with isenrolled =1 and isenrolled =0. I want to select only one rec of SCOTT where his isenrolled =1 and reject the one where isenrolled =0, that way i will get only unique recs for employees who has enrolled and who has not enrolled. How do i select those employees? I tried the qry below and it doesn't work

select * FROM  employee e 
WHERE e.empid not IN( SELECT empid FROM employee  e2
WHERE e2.isenrolled =1)

Answers


First I set up some test data using:

create table t4
as select * from scott.emp

alter table t4 add (isenrolled number(10))

update t4
set isenrolled = 0

insert into t4
(select emp.*, 1
from scott.emp)

At this point the t4 table has now two records for each employee (one where isenrolled = 0 and one where isenrolled = 1)

so I change ALLEN's data so he has "opted" out

delete from t4
where ename = 'ALLEN'
and isenrolled = 1

This query then shows the data for ALLEN (1 record) and SMITH (2 records)

select *
from t4
where ename IN ('ALLEN', 'SMITH')
order by ename

Then to show just one record per employee (restricted to ALLEN and SMITH in this case) you could use:

select t.*
from t4 t
where isenrolled = (select MAX(isenrolled)
                    from t4
                    where t4.empno = t.empno)
and ename IN ('ALLEN', 'SMITH')

Hope this helps


Need Your Help

Remove elements identified by class, which meet id condition

jquery

I want to remove all elements identified by .custom_class, where data-id is less than id-15.

Index was outside the bounds of array at trimmed text

c# arrays indexoutofboundsexception

I have created a Windows Form App which is connected to a database and users enter specific values in it. The problem I'm facing right now is that I created a search, which works like charm. But, u...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.