• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

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
0
Swadhin Ray
Asked:
Swadhin Ray
1 Solution
 
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this?
select col1, col2, col3, max(col4) from yourtable group by col1, col2, col3 

Open in new window

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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now