SQL Challenge, Part 2

[Argo]

Ok, I need one more complicated query. I need to find all duplicate strings in a particular field of the table. The test should be case insensetive, since the table wouldn't contain two exact matches, however it may contain case insensetive matches.

One thing would be to write nested query:

select userid as user1 from table ABC where lower(userid) = lower(select userid as user2 from ABC where user1 != user2)


Now, I'm not sure this will work at all, especiall the second lower(...) part. Any suggestions?

 

[Lanik]

This might help you get started. This will give you a count of all the strings.
This example is of a fictional table with columns UID and TargetField.

SELECT COUNT(UID) AS FieldCount, TargetField
FROM Test3
GROUP BY TargetField
ORDER BY FieldCount DESC

This would give you a table of all the unique strings with their counts. What you'll want to probably do from here is make this a sub statement to another Select statement where you grab the ones with a field count > 1.

 

[SQL]

Combine count with the having clause

select userid from tableabc
group by userid
having count(userid)>1
order by userid;

What database platform is this for?

 

[Argo]

DB2. The problem with the above is that it will return case sensetive results. I.e. if database has 'argo' and 'ARGO' it won't detect them as duplicates.

 

[SQL]

Try using lower() then on the query.

select lower(userid) from tableabc
group by lower(userid)
having count(lower(userid))>1
order by lower(userid);