Favourite music

Thanks to iTunes, sqlTunes, SQL Server and my inner nerd(*), I can report definitively that my top three favourite music album purchases of 2011 were:

  1. Songs Lost and Stolen (2011) – Bella Hardy
  2. Angel Station (1979) – Manfred Mann’s Earth Band
  3. Punching Air In the Nyah EP (2010) – Back Back Forward Punch

(*) who am I kidding with “inner nerd” – I’m nerd all the way to the surface. Here’s the SQL that gave me the results – note the use of various keywords that I added to track comments in iTunes in order to exclude singles, partial albums, or bonus tracks from the computation of ratings.

select album, artist, AVG(Rating) as Rating, COUNT(*) as TrackCount
 from Track T
 where
 ISNULL(Genre, '') <> 'Classical'
 AND ISNULL(Genre, '') <> 'Podcast'
 AND isnull(Comments, '') NOT like '%Single%'
 AND isnull(Comments, '') NOT like '%PartialAlbum%'
 AND isnull(Comments, '') NOT like '%BonusTrack%'
 AND Album is not null
 and (select MAX([Date Added]) from track T1 
      where T1.Album = T.Album and T1.[Date Added] 
      between '1 jan 2011' and '31 dec 2011') is not null
 group by album, artist
 having count(*) > 1
 order by AVG(Rating) desc

[This content was originally posted to Google+]

Leave a Reply

Comments on this site are moderated and will not appear until approved by the website owner. Your email address will not be published. Required fields are marked *