Home » SQL & PL/SQL » SQL & PL/SQL » Need to write an Select for the following result
Need to write an Select for the following result [message #305010] Fri, 07 March 2008 08:55 Go to next message
tptbalaji
Messages: 3
Registered: July 2006
Location: us
Junior Member
hi,

For the following result i need to write a SQL command, is there any body can write it for me.

Col1 Col2 Col3 Col4
XXX 123 a b
XXX 456 a b
XXX 789 a b
XXX 321 a b

Conditions

Need to display Cols only when it has same value for more than once(means at least 2) with various Cols2,Col3,Col4 values


select Col1,Col2,Col3,Col4 from Table A,Table B
where
A.Key = B.key
having count(Col1) >1
group by
Col1,Col2,Col3,Col4

wont give result b'use i dont want to group by Cols2. And i dont want to write a Procedure too. Can we solve thru SQL ?

Any help ? ? ?
Re: Need to write an Select for the following result [message #305011 is a reply to message #305010] Fri, 07 March 2008 08:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
, is there any body can write it for me.
I sincerely hope that nobody here will do your work for you. Supply a test case along with what you have tried and format your code as per the forum guide and we will help you to write your own code.

[Updated on: Fri, 07 March 2008 08:58]

Report message to a moderator

Re: Need to write an Select for the following result [message #305013 is a reply to message #305010] Fri, 07 March 2008 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
I certainly won't because I don't know about A.Key = B.key
Re: Need to write an Select for the following result [message #305017 is a reply to message #305010] Fri, 07 March 2008 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use COUNT in its analytic form.

Regards
Michel
Re: Need to write an Select for the following result [message #305020 is a reply to message #305017] Fri, 07 March 2008 09:35 Go to previous messageGo to next message
tptbalaji
Messages: 3
Registered: July 2006
Location: us
Junior Member
Some more info.

create table t1(col1 number(1) primary key,col2 varchar2(20),col3 varchar2(20));

insert into t1 values(1,'john','alason');
insert into t1 values(2,'steve','pop');
insert into t1 values(3,'williom','John');

create table t2(c1 number(1),c2 varchar2(20),c3 varchar2(20));

insert into t2 values(1,'xxx','123');
insert into t2 values(1,'xxx','456');
insert into t2 values(1,'xxx','789');
insert into t2 values(2,'xxx','123');
insert into t2 values(2,'xxx','987');
insert into t2 values(3,'xxx','567');

The expected result

1 xxx 123 John alason
456 John alason
789 John alason

2 xxx 123 steve pop
987 steve pop


Conditions

Need to list only the rows which are having more than one in t2 Table.
Re: Need to write an Select for the following result [message #305023 is a reply to message #305020] Fri, 07 March 2008 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it as I said.

Regards
Michel
Re: Need to write an Select for the following result [message #305035 is a reply to message #305023] Fri, 07 March 2008 12:47 Go to previous messageGo to next message
tptbalaji
Messages: 3
Registered: July 2006
Location: us
Junior Member
I got the Answer..thanks guys
Re: Need to write an Select for the following result [message #305036 is a reply to message #305035] Fri, 07 March 2008 12:50 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post your solution for others.

Regards
Michel
Previous Topic: SQL query from v$session and v$lock hangs
Next Topic: DEGREE parameter NOT working while gathering INDEX STATS
Goto Forum:
  


Current Time: Wed Dec 07 16:47:26 CST 2016

Total time taken to generate the page: 0.13982 seconds