Home » SQL & PL/SQL » SQL & PL/SQL » Sql query
Sql query [message #223368] Thu, 08 March 2007 12:45 Go to next message
lucky_koti
Messages: 1
Registered: March 2007
Junior Member
Hi all,

I have a oracle table like :

e_no e_id e_name Indicator
1 2 lucky N
1 2 tom N
1 2 dick Y

Unique constriant is on e_no,e_id and e_name
And my requirement is to find out the rows in table where the
Indicator count is greter than 1 for e_no and e_id combination.

plese help me in writing a sql query

Thanks in advance,
LuckY
Re: Sql query [message #223424 is a reply to message #223368] Thu, 08 March 2007 17:47 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Try this.

create table t
( e_no number, e_id number, e_name varchar2(10), indicator varchar2(1) );

Table created.


insert into t values ( 1, 2, 'lucky', 'N'  );

1 row created.

insert into t values ( 1, 2, 'tom'  , 'N'  );

1 row created.

insert into t values ( 1, 2, 'dick' , 'Y'  );

1 row created.

insert into t values ( 1, 3, 'harry', 'Y'  );

1 row created.

insert into t values ( 1, 3, 'joe'  , null );

1 row created.


commit;

Commit complete.


select e_no, e_id, count(indicator) indicator_count
from   t
group by e_no, e_id
having count(indicator) > 1 ;

      E_NO       E_ID INDICATOR_COUNT
---------- ---------- ---------------
         1          2               3

1 row selected.


Previous Topic: Print characters if no rows are selected
Next Topic: How can i calla function with dynamic SQL on ORACLE 7.3
Goto Forum:
  


Current Time: Sat Dec 10 00:59:19 CST 2016

Total time taken to generate the page: 0.15292 seconds