Top of the Charts !

• greg.bull

  SSCrazy

  Points: 2093

  More actions

  August 2, 2011 at 10:47 am

  #242370

  Hi there,

  I need to produce some code to downloads ranking position by music tracks for this month, and last month.

  My data would be 1 row per month for each track, and a metric to show download count for that month, ie:

  datemonth, trackname, downloads

  'June 2011', 'Track 1', 2000

  'July 2011, 'Track 1', 2500

  'June 2011 ' Track 2', 1000

  'July 2011, 'Track 2', 3000

  I can set the month I want to check for, with a where clause - to see the positions for July 2011, I'd just say where datemonth = 'July 2011'.

  Then, I'd as output, I'd like to see:

  CurrentPosition, LastMonthsPosition, Trackname, downloads

  1, 2, 'Track 2', 3000

  2, 1, 'Track 1, 2500

  Can anyone help as I've not used SQL to determine the position of a row in a results set before.

  Thanks, Greg.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  August 2, 2011 at 10:49 am

  #1362341

  ROW_NUMBER() OVER (PARTITION BY ... ORDER BY)