Getting two values for a colums which is not PK
SELECT c_number 
FROM CLAIM 
GROUP BY c_number 
HAVING COUNT(DISTINCT c_closed) &gt; 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.