CODEWITHSUNDEEP
sqlselectdb2
Johnn Amancio
Johnn Amancio

Reputation: 1

Compare two columns in DB2

I need help with a query DB2.

My table looks like this:

IP_ID         IP_TYPE_CDE UPDT_DT_TM                
------------- ----------- --------------------------
47560147.          39 2012-11-06-04.16.21.000000
47560147.         319 2013-06-12-16.54.09.000000
47560002.          39 2012-11-06-04.16.21.000000
47560002.         319 2013-06-12-16.54.09.000000
47560003.          39 2012-11-06-04.16.21.000000
47560003.          39 2013-06-12-16.54.09.000000

I need to scan the database, catch all the IP_ID and compare it to that IP_ID had change in IP_TYPE_CDE.

I.e., in this case it should return only IP_ID 47560147 and 47560002.

Upvotes: 0

Views: 1439

Answers (1)

Mureinik
Mureinik

Reputation: 312219

You could group by ip_id and filter by a having clause that counts the distinct number of ip_type_cdes:

SELECT   ip_id
FROM     mytable
GROUP BY ip_id
HAVING   COUNT (DISTINCT ip_type_cde) > 1

Upvotes: 1

Related Questions