by IanRTaylorUK » Tue Feb 24, 2026 6:52 am
The Purpose of This Query
The primary purpose of this SQL script is to identify track number collisions within your music collection (specifically TrackType 0). Unlike a simple duplicate file finder, this query detects "logical duplicates"—instances where two or more distinct song entries are competing for the same position on the same disc of an album. By excluding SongTitle from the grouping logic, it specifically flags data entry errors where different songs have been accidentally assigned the same track index. This allows you to maintain a clean, chronologically accurate library, ensuring that media players and library managers sort your albums correctly without overlapping or missing tracks.
Code: Select all
SELECT
TrackType,
AlbumArtist,
Artist,
Album,
DiscNumber,
TrackNumber,
SongTitle,
COUNT(*) as Occurrences
FROM Songs
/* Filter: Only include 'Music' (Type 0).
This excludes Podcasts, Videos, and Audiobooks automatically. */
WHERE TrackType = 0
/* Logic: Define what makes a 'duplicate'.
We group by Album and Track details, but NOT SongTitle.
This catches two different songs assigned to the same track number. */
GROUP BY
AlbumArtist,
Album,
DiscNumber,
TrackNumber
/* Filter results to show only the collisions (where count is 2 or more) */
HAVING Occurrences > 1
/* Sorting: Prioritize the worst offenders (highest Occurrences) first,
then organize alphabetically using your custom IUNICODE collation. */
ORDER BY
TrackType ASC,
Occurrences DESC,
AlbumArtist COLLATE IUNICODE,
Album COLLATE IUNICODE,
DiscNumber COLLATE IUNICODE,
TrackNumber COLLATE IUNICODE;
[b]The Purpose of This Query[/b]
The primary purpose of this SQL script is to identify track number collisions within your music collection (specifically TrackType 0). Unlike a simple duplicate file finder, this query detects "logical duplicates"—instances where two or more distinct song entries are competing for the same position on the same disc of an album. By excluding SongTitle from the grouping logic, it specifically flags data entry errors where different songs have been accidentally assigned the same track index. This allows you to maintain a clean, chronologically accurate library, ensuring that media players and library managers sort your albums correctly without overlapping or missing tracks.
[code]
SELECT
TrackType,
AlbumArtist,
Artist,
Album,
DiscNumber,
TrackNumber,
SongTitle,
COUNT(*) as Occurrences
FROM Songs
/* Filter: Only include 'Music' (Type 0).
This excludes Podcasts, Videos, and Audiobooks automatically. */
WHERE TrackType = 0
/* Logic: Define what makes a 'duplicate'.
We group by Album and Track details, but NOT SongTitle.
This catches two different songs assigned to the same track number. */
GROUP BY
AlbumArtist,
Album,
DiscNumber,
TrackNumber
/* Filter results to show only the collisions (where count is 2 or more) */
HAVING Occurrences > 1
/* Sorting: Prioritize the worst offenders (highest Occurrences) first,
then organize alphabetically using your custom IUNICODE collation. */
ORDER BY
TrackType ASC,
Occurrences DESC,
AlbumArtist COLLATE IUNICODE,
Album COLLATE IUNICODE,
DiscNumber COLLATE IUNICODE,
TrackNumber COLLATE IUNICODE;
[/code]