SQL query to find if multiple instances of a keyword exist

[Timothy_Shepler]

I am looking for a SQL query to search for if multiple instances of a keyword exist.

[Brad_Phillips · Accepted solution]

Tim,

Here's an example of me accomplishing this on my test system.  You will need to change the names of the table and columns, but the logic is the same:

select keyvaluesmall, count(*)
From hsi.keyitem102
group by keyvaluesmall
having count(*) >= 2

[Scott_Hirsch]

I have done this in WF with Regular Expressions to count the number of instances before (you could check "if more than 1").  Are you just doing this for a SQL Report?

[Brad_Phillips · Accepted solution]

Tim,

Here's an example of me accomplishing this on my test system.  You will need to change the names of the table and columns, but the logic is the same:

select keyvaluesmall, count(*)
From hsi.keyitem102
group by keyvaluesmall
having count(*) >= 2

[Aaron_Truskot]

It's worth noting that this query assumes that you are looking for multiple instances of a Keyword Type on documents that are only using a standalone or "loose" Keyword Type. You'll need rule out the use of Multi-Instance Keyword Groups (MIKG) before relying on the information found in this query.

[Danielle_Durda]

This logic was helpful for me. I was trying to find how many documents had +1 instance of a keyword. I just switched the keyvaluesmall for itemnum and it gives you a listing of handles that have +1 instance of the keyword. As it's written above, it will show you keyword values that occur more than once, regardless of which document the instances are attached to.