# Find unique titles for branches with the same parent org
**URL:** https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488
**Category:** Polaris Repository
**Created:** [May 22, 2025, 10:41pm UTC](https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488 "2025-05-22T22:41:03Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@wesochuck](https://forum.innovativeusers.org/u/wesochuck)
#### Post date: [May 22, 2025, 10:41pm UTC](https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488/1 "2025-05-22T22:41:03Z")
This is an adaption of a script we had that **has not been fully tested**. Feel free to edit the script using the wiki function or add a reply with any comments. If you’re looking to see which branches have more than one copy of a title for example for weeding purposes, look at this thread: [SQL for Duplicate Titles](https://forum.innovativeusers.org/t/sql-for-duplicate-titles/1992/)
This script identifies titles held by only one branch within the same parent organization. For these titles, the query shows:
1. The item with the oldest (or `NULL`) `LastCircTransactionDate`.
2. A total count of all items for that unique title at its single owning branch.
**The Logic Overview (Using CTEs):**
1. **`BibsOwnedBySingleBranch` CTE:** Identifies titles exclusive to a single branch, respecting any material type exclusions you define, and notes that branch’s `OwningBranchID`.
2. **`ItemsAndCountsForUniqueBibs` CTE:** Fetches all items for these titles _from their specific owning branch_, again respecting material type exclusions. It also calculates a total count of items per title at that branch using `COUNT(...) OVER (PARTITION BY ...)`.
3. **`RankedItems` CTE:** Ranks items within each title, prioritizing `NULL` `LastCircTransactionDate` values as “oldest”, then by the actual oldest date, using `ROW_NUMBER()`.
4. **Final `SELECT`:** Selects the top-ranked item (`rn = 1`) for each title, displaying its details along with the total item count.
**Flexible Material Type Exclusions:**
The query starts by declaring a table variable named `@ExcludedMaterialTypes`.
- **To exclude specific material types:** `INSERT` their `MaterialTypeID`s into this `@ExcludedMaterialTypes` table.
- **To include all material types (i.e., no exclusions):** Simply leave the `@ExcludedMaterialTypes` table empty (do not run any `INSERT` statements into it).
The query’s `WHERE` clauses then use `NOT EXISTS` to check items against this list.
Sample output:
```auto
-- Declare and set the Parent OrganizationID
DECLARE @ParentOrgID INT;
SET @ParentOrgID = 8; -- Replace 8 with the actual OrganizationID of the parent organization
-- Declare the table variable for excluded MaterialTypeIDs
DECLARE @ExcludedMaterialTypes TABLE (MaterialTypeID INT PRIMARY KEY);
-- HOW TO USE THE EXCLUSION LIST:
-- Scenario 1: To exclude specific MaterialTypeIDs (e.g., 47 and 55)
-- Uncomment and modify the INSERT statement below.
-- INSERT INTO @ExcludedMaterialTypes (MaterialTypeID) VALUES (47), (55);
-- Scenario 2: To NOT exclude any MaterialTypeIDs (i.e., include all material types)
-- Leave the @ExcludedMaterialTypes table empty by keeping the INSERT statement commented out.
WITH BibsOwnedBySingleBranch AS (
SELECT
cir.AssociatedBibRecordID,
MIN(cir.AssignedBranchID) AS OwningBranchID
FROM
polaris.Polaris.CircItemRecords cir
INNER JOIN
polaris.Polaris.Organizations branch_org ON cir.AssignedBranchID = branch_org.OrganizationID
INNER JOIN
polaris.Polaris.Organizations parent_org ON branch_org.ParentOrganizationID = parent_org.OrganizationID
WHERE
parent_org.OrganizationID = @ParentOrgID
AND cir.RecordStatusID = 1
-- MODIFIED: Check against the @ExcludedMaterialTypes table variable
AND NOT EXISTS (SELECT 1 FROM @ExcludedMaterialTypes emt WHERE emt.MaterialTypeID = cir.MaterialTypeID)
GROUP BY
cir.AssociatedBibRecordID
HAVING
COUNT(DISTINCT cir.AssignedBranchID) = 1
),
ItemsAndCountsForUniqueBibs AS (
SELECT
cir.AssociatedBibRecordID,
br.BrowseTitle,
cir.ItemRecordID,
cir.Barcode,
cir.LastCircTransactionDate,
bosb.OwningBranchID,
parent_org_details.OrganizationID AS ActualParentOrgID,
parent_org_details.Name AS ParentOrganizationNameString,
COUNT(cir.ItemRecordID) OVER (PARTITION BY cir.AssociatedBibRecordID) AS TotalItemsAtBranchForBib
FROM
polaris.Polaris.CircItemRecords cir
INNER JOIN
BibsOwnedBySingleBranch bosb ON cir.AssociatedBibRecordID = bosb.AssociatedBibRecordID
AND cir.AssignedBranchID = bosb.OwningBranchID
INNER JOIN
polaris.Polaris.BibliographicRecords br ON cir.AssociatedBibRecordID = br.BibliographicRecordID
INNER JOIN
polaris.Polaris.Organizations item_branch_org ON cir.AssignedBranchID = item_branch_org.OrganizationID
INNER JOIN
polaris.Polaris.Organizations parent_org_details ON item_branch_org.ParentOrganizationID = parent_org_details.OrganizationID
WHERE
parent_org_details.OrganizationID = @ParentOrgID
AND cir.RecordStatusID = 1
-- MODIFIED: Check against the @ExcludedMaterialTypes table variable
AND NOT EXISTS (SELECT 1 FROM @ExcludedMaterialTypes emt WHERE emt.MaterialTypeID = cir.MaterialTypeID)
),
RankedItems AS (
SELECT
iac.*,
ROW_NUMBER() OVER (
PARTITION BY iac.AssociatedBibRecordID
ORDER BY
CASE WHEN iac.LastCircTransactionDate IS NULL THEN 0 ELSE 1 END ASC,
iac.LastCircTransactionDate ASC,
iac.ItemRecordID ASC
) AS rn
FROM
ItemsAndCountsForUniqueBibs iac
)
SELECT
ri.BrowseTitle,
ri.AssociatedBibRecordID,
ri.Barcode,
ri.LastCircTransactionDate,
branch_details.Name AS ItemBranchName,
ri.ParentOrganizationNameString AS ParentOrganizationName,
ri.TotalItemsAtBranchForBib
FROM
RankedItems ri
INNER JOIN
polaris.Polaris.Organizations branch_details ON ri.OwningBranchID = branch_details.OrganizationID
WHERE
ri.rn = 1
AND ri.ActualParentOrgID = @ParentOrgID
ORDER BY
ri.BrowseTitle,
ri.Barcode;
```
---
### Author:  [@bgaydos](https://forum.innovativeusers.org/u/bgaydos)
#### Post date: [May 23, 2025, 11:21am UTC](https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488/2 "2025-05-23T11:21:56Z")
Hi @wesochuck. Maybe I’m missing something, but wouldn’t the window function `DENSE_RANK()` be a more appropriate choice than `ROW_NUMBER()` in the **`RankedItems`** CTE? Or is the idea to avoid ties?
---
### Author:  [@wesochuck](https://forum.innovativeusers.org/u/wesochuck)
#### Post date: [May 23, 2025, 11:45am UTC](https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488/3 "2025-05-23T11:45:36Z")
It is correct that we don’t want ties, and of course a downside to vibe coding is that you’re more likely to get the most commonly used thing out there, and I think `ROW_NUMBER()` has more frequent appearances out in the wild.
---
### Author:  [@bgaydos](https://forum.innovativeusers.org/u/bgaydos)
#### Post date: [May 23, 2025, 12:51pm UTC](https://forum.innovativeusers.org/t/find-unique-titles-for-branches-with-the-same-parent-org/2488/4 "2025-05-23T12:51:30Z")
Given my fondness for my Luddite flip phone, it won’t surprise you to learn that I have not dabbled in AI SQL generation (or AI anything else).
One day the SkyNet chatbot will respond with **`;DROP DATABASE [Humanity];`** 🤓