I am trying to build one procedure to take a large amount of data and create 5 range buckets to display the data. the buckets ranges will have to be set according to the results.

Here is my existing SP

GO
/****** Object:  StoredProcedure [dbo].[sp_GetRangeCounts]    Script Date: 03/28/2010 19:50:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetRangeCounts]
    @idMenu int

AS

declare 
@myMin decimal(19,2),
@myMax decimal(19,2),
@myDif decimal(19,2),
@range1 decimal(19,2),
@range2 decimal(19,2), 
@range3 decimal(19,2),
@range4 decimal(19,2),
@range5 decimal(19,2),
@range6 decimal(19,2)

SELECT @myMin=Min(modelpropvalue), @myMax=Max(modelpropvalue)
FROM xmodelpropertyvalues where modelPropUnitDescriptionID=@idMenu 

set @myDif=(@myMax-@myMin)/5
set @range1=@myMin
set @range2=@myMin+@myDif
set @range3=@range2+@myDif
set @range4=@range3+@myDif
set @range5=@range4+@myDif
set @range6=@range5+@myDif

select @myMin,@myMax,@myDif,@range1,@range2,@range3,@range4,@range5,@range6

select t.range as myRange, count(*) as myCount
from (
  select case  
    when modelpropvalue between @range1 and @range2 then 'range1'
    when modelpropvalue between @range2 and @range3 then 'range2'
    when modelpropvalue between @range3 and @range4 then 'range3'
    when modelpropvalue between @range4 and @range5 then 'range4'
    when modelpropvalue between @range5 and @range6 then 'range5'
     end as range
  from xmodelpropertyvalues where modelpropunitDescriptionID=@idmenu) t
group by t.range order by t.range

This calculates the min and max value from my table, works out the difference between the two and creates 5 buckets. The problem is that if there are a small amount of very high (or very low) values then the buckets will appear very distorted - as in these results...

range1  2806
range2  296
range3  75
range5  1

Basically I want to rebuild the SP so it creates buckets with equal amounts of results in each. I have played around with some of the following approaches without quite nailing it...

SELECT modelpropvalue, NTILE(5) OVER (ORDER BY modelpropvalue) FROM xmodelpropertyvalues - this creates a new column with either 1,2,3,4 or 5 in it

 ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range1 and @range2
 ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range2 and @range3

• or maybe i could allocate every record a row number then divide into ranges from this?