Home » SQL & PL/SQL » SQL & PL/SQL » Can this be achieved without using EXISTS
Can this be achieved without using EXISTS [message #238546] Fri, 18 May 2007 08:38 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

I want to write a query which could fetch records of this type


TABLE1

COL1   COL2
1	1
1	2
1	3

2	3
2	2

3	1
3	1
3	1

4	1
4	2
4	3
4	4


I want to list the distinct col1 column where there is a occurance of col2 value "1" and value "2" exists.

So this query would result in

COL1   
1	
4


without using exist clause.

Srivaths
Re: Can this be achieved without using EXISTS [message #238549 is a reply to message #238546] Fri, 18 May 2007 08:44 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

As Tom K. says: Analytics rock, Analytics roll:

select col1 from (
select col1, col2,lead(col2) over (partition by col1 order by col2) nxt from table1)
where col2=1 and nxt=2
Re: Can this be achieved without using EXISTS [message #238554 is a reply to message #238546] Fri, 18 May 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select col1
from table1
group by col1
having sum(decode(col2,1,1,2,1000,0))>=1001
/

(Assuming there are less than 1000 rows with col2=1 and same col1)

Analytics is not always the right way. (Also T. Kyte) Smile

Regards
Michel
Re: Can this be achieved without using EXISTS [message #238558 is a reply to message #238554] Fri, 18 May 2007 09:03 Go to previous messageGo to next message
Alien
Messages: 245
Registered: June 1999
Senior Member
Hi,

SQL> insert into table1 values (2,2);

1 rij is aangemaakt.

SQL> /

1 rij is aangemaakt.

SQL> commit
  2  ;

Commit is voltooid.

SQL> select * from table1
  2  ;

      COL1       COL2
---------- ----------
         2          2
         2          2

SQL> select col1
  2  from table1
  3  group by col1
  4  having sum(decode(col2,1,1,2,1000,0))>=1001
  5  /


      COL1
----------
         2



Quote:
Analytics is not always the right way.



So very true.
Re: Can this be achieved without using EXISTS [message #238562 is a reply to message #238546] Fri, 18 May 2007 09:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm sure there are lots of ways to solve a learning assignment like this. Hopefully the OP will do one himself.

MYDBA@orcl > start counter;
MYDBA@orcl > create table t (a number, b number);

Table created.

MYDBA@orcl >
MYDBA@orcl > set feedback off
MYDBA@orcl >
MYDBA@orcl > insert into t values (1,1);
MYDBA@orcl > insert into t values (1,2);
MYDBA@orcl > insert into t values (1,3);
MYDBA@orcl >
MYDBA@orcl > insert into t values (2,0);
MYDBA@orcl > insert into t values (2,4);
MYDBA@orcl >
MYDBA@orcl > insert into t values (3,2);
MYDBA@orcl > insert into t values (3,3);
MYDBA@orcl >
MYDBA@orcl > insert into t values (4,1);
MYDBA@orcl > insert into t values (4,1);
MYDBA@orcl > insert into t values (4,1);
MYDBA@orcl >
MYDBA@orcl > insert into t values (5,0);
MYDBA@orcl > insert into t values (5,1);
MYDBA@orcl > insert into t values (5,2);
MYDBA@orcl >
MYDBA@orcl > set feedback on
MYDBA@orcl >
MYDBA@orcl > commit;

Commit complete.

MYDBA@orcl >
MYDBA@orcl > break on a skip 1 duplicates
MYDBA@orcl > select * from t order by a,b;

         A          B
---------- ----------
         1          1
         1          2
         1          3

         2          0
         2          4

         3          2
         3          3

         4          1
         4          1
         4          1

         5          0
         5          1
         5          2


13 rows selected.

MYDBA@orcl > clear breaks
MYDBA@orcl >
MYDBA@orcl > select a from
  2  (select distinct a,b from t where b in (1,2))
  3  group by a
  4  having count(*) = 2;

         A
----------
         1
         5

2 rows selected.

MYDBA@orcl >
MYDBA@orcl > drop table t;

Table dropped.

MYDBA@orcl >

Re: Can this be achieved without using EXISTS [message #238565 is a reply to message #238558] Fri, 18 May 2007 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a small modification:
select col1
from table1
group by col1
having sum(decode(col2,1,1,0))>=1
   and sum(decode(col2,2,1,0))>=1
/

Try to be tricky but miss it. Uh Oh

Regards
Michel
Re: Can this be achieved without using EXISTS [message #238696 is a reply to message #238546] Sat, 19 May 2007 04:56 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Thank you Alien , Michel , smartin. To be frank I am bit afraid of Analytical functions.

I had to make a query which could be dynamically framed on the col2 like
Only "1 and 2" or "1 or 2 or 3" or ....

Thank you very much.

regards
Srivaths

Previous Topic: client_info is not working (merged)
Next Topic: problem with to_date function format..
Goto Forum:
  


Current Time: Sat Dec 03 15:57:05 CST 2016

Total time taken to generate the page: 0.14270 seconds