Monthly Archives: December 2011

Post navigation

Four years ago I attempted to walk the Wishing Well track in the Watagans, but had to abort half way when my children got tangled in some thorny undergrowth (minor inconvenience) which unfortunately was atop a bull ants nest (major drama).

Today, with two of my children, with plenty of resolve, and much attentiveness, we finally completed the walk.

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:

Songs Lost and Stolen (2011) – Bella Hardy

Angel Station (1979) – Manfred Mann’s Earth Band

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