0

I have a table with 4 columns

id,C_number,C_reopen,c_closed

Where id is the pk. I want to pull rows which has two different values for c_closed for the same c_number. Not c_number is not pk hence can have duplicate values.

create table claim 
( 
      id not null number(10),
      c_number string(50), 
      c_reopen date,
      c_closed(date)
);

Sample data:

insert into claim(111 , 'c101', '10/1/12015','15/1/2015');
insert into claim(112, 'c102','10/2/2015','15/2/2015');
insert into claim(113,'c101','18/1/2015','28/1/2015');

So, here for the same c_number-c101, I have two closed one is 15/1/2015 and the other 28/1/2015. I want a query to retrieve c_cumber which have more than one value for close.

0

1 Answer 1

3
SELECT c_number 
FROM CLAIM 
GROUP BY c_number 
HAVING COUNT(DISTINCT c_closed) > 1; 

As we have to retrieve the c_number where the rows have more than one unique c_closed value for every c_number , we have to do the following things:
1. Group the rows by c_number , so we will get rows for every unique c_number.
2. Within every group (each unique value of c_number causes a new group, 10 unique c_number = 10 groups) use HAVING CLAUSE to filter out groups where each unique c_number has only one unique c_closed value.

To count only distinct c_closed values you have to use DISTINCT.
HAVING CLAUSE it is like WHERE CLAUSE but it is used after aggregation when all rows with the same values of chosen columns collapse into one row.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.