comparing each record of a table for some columns

Question

I have a table TEST which has like this records

ID      USERNAME       IPADDRS       CONNTIME   country
8238237     XYZ        10.16.199.20  11:00:00   USA
8255237     XYZ        10.16.199.20  11:00:00   UK
485337      ABC        10.16.199.22  12:25:00   UK
8238237     ABC        10.16.199.23  02:45:00   INDIA

I have to compare each record and has to get ID value of the records which has the country column as UK and having same USERNAME,IPADDRS and CONNTME.

means USERNAME,IPADDRSS,CONNTIME should be equal but final filter will go on country UK.

so output will be ID=8255237 for above Table.

Appreciate your help.Thanks!

Answer 1

Well, SQL is descriptive. So you should describe what you want. How about this?

select a.id from ip a where a.country='UK' and (a.username,a.ipaddrs,a.conntime) in (select username,ipaddrs,conntime from ip where country<>'UK')

Basically you select the ID for those that match the required triplet, but the matching record should not be from UK. This is basic SQL and should run on all systems. Disclaimer: You might need indexes for performance.

Answer 2

Try this:

SELECT a.ID FROM (SELECT ID,USERNAME,IPADDRS,CONNTIME,COUNTRY,ROW_NUMBER()OVER(PARTITION BY USERNAME,IPADDRS,CONNTIME ORDER BY USERNAME,IPADDRS,CONNTIME) AS seq  
FROM EMP_IP) a WHERE a.COUNTRY = 'UK' AND a.seq > 1;