SQL for Duplicate Titles

Polaris
1

Calling all SQL Superheroes!

We are trying to generate a list of multiple copies in our Fiction section. I’ve tinkered a bit and checked out Daniel’s Amazing Compendium of SQL delights, but I couldn’t find this particular query.

Does anyone have this handy by chance, that they’re willing to share? :wink:

Thanks so much!

Amie

2

Would something like this work for you?

select browsetitle,max(callnumber) as callnumber,count(circitemrecords.itemrecordid) from circitemrecords with (nolock)
left join itemrecorddetails with (nolock) on circitemrecords.itemrecordid=itemrecorddetails.itemrecordid --just for the call number
left join bibliographicrecords with (nolock) on circitemrecords.associatedbibrecordid=bibliographicrecords.bibliographicrecordid
where assignedbranchid=3 --assuming you only want duplicates at one location
and shelflocationid=437 --assuming your fiction section is defined by shelf location 
group by browsetitle --these 2 lines (group and having count) are what pull the duplicates
having count(circitemrecords.itemrecordid)>1 
order by callnumber

If you’re finding it pulls a lot of false positives (e.g. in our collection we have a copy of Homeland by Barbara Kingsolver and Homeland by Fernando Aramburu - those come up as duplicates when just matching on the title), you could experiment with matching on the author as well, or pulling a list of the call numbers instead of just one of them for ease of troubleshooting:

select browsetitle,string_agg(callnumber,', ') as callnumber,count(circitemrecords.itemrecordid) from circitemrecords with (nolock)
left join itemrecorddetails with (nolock) on circitemrecords.itemrecordid=itemrecorddetails.itemrecordid 
left join bibliographicrecords with (nolock) on circitemrecords.associatedbibrecordid=bibliographicrecords.bibliographicrecordid
where assignedbranchid=3 
and shelflocationid=437
group by browsetitle,soundex(browseauthor) 
--using the soundex means that slight variations on how the author's name is stored won't 
--prevent a match, but significant differences like kingsolver vs aramburu won't match)
having count(circitemrecords.itemrecordid)>1 
order by callnumber
1 Like
3

Hi Amie,
I create lists of multiple copies by using Simply Reports. This can be done as an item count report if you out put the bibliographic data to the item count report. This is an example of the configuration:

Simply Reports Tab: Items

Submenu: Item count reports

Columns selected for output:

  • MARC bibliographic record ID
  • MARC author
  • MARC title
  • Item assigned branch name
  • Item assigned collection name
  • Item material type

Filters:

Item general filters:

  • Assigned Branch
  • Collection
  • Record status: Final
  • Material type (optional)
  • Shelf location (optional)
  • Statistical code (optional)

Item relative date filters (prior to a specified date):

  • Last check out or renewal date more than: (specify number) years before the report run date

Item circulation filters:

  • Lifetime circulation less than or equal to your specified number

NOTE: You can sort by the number of items in Excel.

Rachel Fischer

1 Like