CODEWITHSUNDEEP
mysqlsqlite
Brandon Minton
Brandon Minton

Reputation: 1004

Select duplicates from a single row?

Here I have a table called CoreTracks: 

+---------+-----------------+----------+----------+----------+---------+
| TrackId |       URI       | ArtistID |  Title   | FileSize | BitRate |
+---------+-----------------+----------+----------+----------+---------+
|  1      | /home/music/... |   234    | atune    |  8958223 |   192   |
|  2      | /home/music/... |   427    | goodsong |  6954373 |   192   |
|  3      | /home/music/... |   427    | goodsong |  4695698 |   128   |
|  4      | /home/music/... |   427    | goodsong |  5839962 |   160   |
|  5      | /home/music/... |   427    | goodsong |  4695698 |   128   |
|  6      | /home/music/... |   522    | another  |  3458859 |   128   |
+---------+-----------------+----------+----------+----------+---------+

What I want retrieved is this:

+---------+-----------------+----------+----------+----------+---------+
| TrackId |       URI       | ArtistID |  Title   | FileSize | BitRate |
+---------+-----------------+----------+----------+----------+---------+
|  3      | /home/music/... |   427    | goodsong |  4695698 |   128   |
|  4      | /home/music/... |   427    | goodsong |  5839962 |   160   |
|  5      | /home/music/... |   427    | goodsong |  4695698 |   128   |
+---------+-----------------+----------+----------+----------+---------+

I am trying to remove duplicates based on having the same title, same artist id, and a different track id while not returning the entry with the highest bitrate and the highest filesize.

What I have so far is this:

SELECT * FROM CoreTracks 
WHERE Title = Title AND ArtistID = ArtistID 
AND BitRate != (SELECT MAX(BitRate) FROM CoreTracks WHERE Title = Title AND ArtistID = ArtistID) 
AND FileSize != (SELECT MAX(FileSize) FROM CoreTracks WHERE Title = Title AND ArtistID = ArtistID);

Which returns every track. What am I missing to make this query work?

Upvotes: 2

Views: 117

Answers (4)

user1285324
user1285324

Reputation:

Try self join instead of normal query. I didnot tried it.

Upvotes: 0

kasavbere
kasavbere

Reputation: 6003

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.

Upvotes: 0

Glenn
Glenn

Reputation: 9150

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
          GROUP BY 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
          GROUP BY Title, ArtistID) c2
  WHERE c1.Title = c2.Title
    AND c1.ArtistID = c2.ArtistID
    AND (c1.FileSize != c2.maxFileSize AND c1.BitRate != c2.maxBitRate)

Upvotes: 2

Scott Corscadden
Scott Corscadden

Reputation: 2860

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

Upvotes: 0

Related Questions