Select Duplicates From A Single Row?

Here I have a table called CoreTracks: +---------+-----------------+----------+----------+----------+---------+ | TrackId | URI | ArtistID | Title | FileSize | BitR

Solution 1:

This would get the inverse (ie skip the duplicates):

SELECT c1.*FROM CoreTracks c1
      ,(SELECT Title, ArtistID, MAX(FileSize) AS maxFileSize, MAX(BitRate) maxBitRate
          FROM CoreTracks
          GROUPBY Title, ArtistID) c2
  WHERE c1.Title = c2.Title
    AND c1.ArtistID = c2.ArtistID
    AND (c1.FileSize = c2.maxFileSize OR c1.BitRate = c2.maxBitRate)

And the duplicates:

SELECT c1.*FROM CoreTracks c1
      ,(SELECT Title, ArtistID, MAX(FileSize) AS maxFileSize, MAX(BitRate) maxBitRate
          FROM CoreTracks
          GROUPBY Title, ArtistID) c2
  WHERE c1.Title = c2.Title
    AND c1.ArtistID = c2.ArtistID
    AND (c1.FileSize != c2.maxFileSize AND c1.BitRate != c2.maxBitRate)

Solution 2:

Select max(trackId) from group by Title, ArtistID sort by BitRate asc - then wrap that in another select by the trackId?

Solution 3:

SELECT A.*FROM CoreTracks A, CoreTracks B 
WHERE A.Title = B.Title AND A.ArtistID = B.ArtistID AND A.trackId != B.trackId
HAVING A.BitRate !=MAX(A.BitRate) AND A.FileSize !=MAX(A.FileSize);

Not yet tested but should work.