sql in oracle

Hello Experts,

I have a table as like below :

create table DETAILS
(
COL1 number,
COL2 varchar2(80),
COL3 varchar2(80),
col4 varchar2(50)
);

Open in new window


Having the records like:

OUTPUT:
col1             col2            col3           col4
156618224	1.9.17.10	1.9.17.10	switch
156618224	1.9.17.10	1.9.17.10	null
156613324	1.9.16.10	1.9.16.10	null
156613324	1.9.16.10	1.9.16.10	router
156614444	1.9.10.99	1.9.10.99	xxxxx

Open in new window



Need a query which will give me the output like below:

col1             col2            col3           col4
156618224	1.9.17.10	1.9.17.10	switch
156613324	1.9.16.10	1.9.16.10	router
156614444	1.9.10.99	1.9.10.99	xxxxx

Open in new window


Now here the col2 = col3 and based on that I need to find it for more than 1 million records.

Thanks,
Sloba
LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
something like this?
select col1, col2, col3, max(col4) from yourtable group by col1, col2, col3 

Open in new window

0
 
SharathData EngineerCommented:
You haven't provided more details. Are you looking for this?
SELECT * FROM DETAILS WHERE col4 IS NOT NULL

Open in new window

0
 
kristof1104Commented:
Here you go:
SELECT * FROM DETAILS WHERE col2 = col3

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ken ButtersCommented:
if you also need col2 and col3 to be equal:

SELECT * FROM DETAILS WHERE col4 IS NOT NULL and col2 = col3;
0
 
kristof1104Commented:
sorry forgot the distinct!

SELECT distinct * FROM DETAILS WHERE col2 = col3
                                            

Open in new window

0
 
plusone3055Commented:
select column2, column3,  
   case
      when column2 is NULL and column3 is NULL then 'true'  
      when column2=column3 then 'true'  
      else 'false'  
   end  
from DETAILS ;
0
 
plusone3055Commented:
if you need to consider  both nulls as well (which you probably do)



select column2, column3,  
   case
      when column2 is NULL and column3 is NULL then 'true'  
      when column2=column3 then 'true'  
      else 'false'  
   end  
from DETAILS ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.