Home » SQL & PL/SQL » SQL & PL/SQL » Help on subquery
Help on subquery [message #227781] Thu, 29 March 2007 07:14 Go to next message
amitora
Messages: 2
Registered: March 2007
Junior Member
Sorry forgot to put Values...in the Inserts


I have a table which has two columns

Create table Dead
chq_no number(6)
proposal_no number(10)
)

Insert into x values(101,12345);
Insert into x values(100,678910);
Insert into x values(100,235642);
Insert into x values(101,457895);
Insert into x values(102,678910);

I need a query which would pull up chq_no which are same for more than 1 proposal_no

[Updated on: Thu, 29 March 2007 07:16]

Report message to a moderator

Re: Help on subquery [message #227791 is a reply to message #227781] Thu, 29 March 2007 07:27 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select chq_no, count(*)
from
(select distinct chq_no, proposal_no
from x)
GROUP BY chq_no
having count(*) > 1
Re: Help on subquery [message #227794 is a reply to message #227791] Thu, 29 March 2007 07:30 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Create table test_tab
(chq_no number(6),
proposal_no number(10)
)

Insert into test_tab values(101,12345);

Insert into test_tab values(100,678910);

Insert into test_tab values(100,235642);

Insert into test_tab values(101,457895);

Insert into test_tab values(102,678910);

select * from test_tab;

chq_no proposal_no
---------------------
101 12345
100 678910
100 235642
101 457895
102 678910

select * from test_tab where chq_no in (select chq_no from test_tab group by chq_no having count(*)>1);

chq_no proposal_no
------------------------
100 678910
100 235642
101 12345
101 457895
Re: Help on subquery [message #227795 is a reply to message #227781] Thu, 29 March 2007 07:31 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Or you can use partitioning:

select chq_no, proposal_no
from
(select chq_no, proposal_no,
count(distinct proposal_no) over (partition by chq_no) as cnt
from x)
where cnt > 1

[Updated on: Thu, 29 March 2007 07:32]

Report message to a moderator

Re: Help on subquery [message #227802 is a reply to message #227795] Thu, 29 March 2007 07:41 Go to previous message
amitora
Messages: 2
Registered: March 2007
Junior Member
Thank YOu Guys !!

You save my Day .....Appreciate it !!!
Razz
Previous Topic: Pagination (Merged)
Next Topic: Changing the schema within procedure
Goto Forum:
  


Current Time: Fri Dec 09 13:47:11 CST 2016

Total time taken to generate the page: 0.24313 seconds