Generate a number of ranges for a random set of values

Given a set of random numeric values in a database, how do I generate a limited list of ranges where each range contains at least one value? The ranges should not overlap and ideally have a similar amount of values in them. Ideally their boundaries should also be multiples of 10, 100, 1000 etc...

For example:

Values: 100,150,180,300,400,500,600,650,700
results in
4 ranges: 100-180(2), 180-300(1), 300-600(3), 600-800(3)

How could this be done in C# or T-SQL?

Thanks

From stackoverflow

• In MS SQL 2005+:

  SELECT range, (MIN(getprev) + MIN(value)) / 2 as range_start, (MAX(getnext) + MAX(value)) / 2 AS range_end, COUNT(*) as range_values
  FROM (
    SELECT value,
           NTILE(4) OVER (ORDER BY value ) AS range,
           (
           SELECT TOP 1 value
           FROM values li
           WHERE li.value < lo.value
           ORDER BY
             li.value DESC
           ) AS getprev,
           (
           SELECT TOP 1 value
           FROM values li
           WHERE li.value > lo.value
           ORDER BY
             li.value
           ) AS getnext
    FROM values lo
  ) vo
  GROUP BY range
  ORDER BY range
  

  In your case:

  1   100   240   3
  2   240   450   2
  3   450   625   2
  4   625   700   2
  

  AndyD : Can you do this in Linq? :)

  Quassnoi : You can do this in a stored procedure and call it from LINQ :)

• Assume we want ranges with one value in them each: Values: 100 150 180 300 Just produce these random numbers:

  - a: 0 <= 100, eg: 50
  - b: 100 <= 150, eg: 125
  - c: 150 <= 180, eg: 165
  - d: 180 <= 300, eg: 200
  - e: 300 <=    , eg: 350
  

  ...

  Ranges: 50-125 (1), 125-180(1), 180-200(1), 200 - 350 (1) ...: each one contains exactly one number.

  Now to have more then one number in each range, just start skippin numbers, for example skip c (can pick a random chance to skip one): skip c:

  50-125(1), 125-200(2), 200-350(1)
  

  AndyD : I'm sure if I get your answer... could you write some pseudo code for your algorithm?