Get count for each row

• landingnormand

  SSC Enthusiast

  Points: 112

  More actions

  January 6, 2011 at 8:50 am

  #243235

  Hi,

  If given a table comprising of two columns, [ID] and [Value] for example, how do I for each row get the number of rows where the [Value] is less than the current rows [Value]. For example given the following records:

  ID, Value

  1, 5

  2, 10

  3, 3

  4, 7

  5, 11

  6, 2

  I would get

  ID, Value, Count

  1, 5, 2

  2, 10, 4

  3, 3, 1

  4, 7, 3

  5, 11, 5

  6, 2, 0

  in the first row the count indicates that there are 2 rows with a value less than 5 etc.

  Thanks

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 6, 2011 at 8:55 am

  #1270153

  Are there any duplicates?

  If not, you could simply use

  ROW_NUMBER() OVER(ORDER BY Value) - 1

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• landingnormand

  SSC Enthusiast

  Points: 112

  More actions

  January 6, 2011 at 10:59 am

  #1270213

  Hi,

  Sorry, yes I forgot to mention that there can be duplicate [Value] values (but not [ID] values). So I could have:

  ID, Value

  1, 5

  2, 10

  3, 3

  4, 7

  5, 11

  6, 2

  7, 11

  8, 2

  9, 5

  Thanks again

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  January 6, 2011 at 11:19 am

  #1270223

  Use RANK() instead of ROW_NUMBER()

  RANK() OVER(ORDER BY Value) - 1