sql server - SQL: Arrange numbers on a scale 1 to 10 -

i have table in sql server 2008 database number column want arrange on scale 1 10.

here example column (scale) want accomplish sql

name    count   (scale) ----------------------       19      2        b       1       1 c       25      3 d       100     10 e       29      3 f       60      7    

in example above min , max count 1 , 100 (this different day day).

i want number each record belongs to.

1 = 0-9 2 = 10-19 3 = 20-29 , on... 

it has dynamic because data changes everyday can not use where clause static numbers this: when count between 0 , 10...

try this, though note technically value 100 doesn't fall in range 90-99 , therefore should classed 11, hence why value 60 comes out scale of 6 rather 7:

sql fiddle

ms sql server 2008 schema setup:

query 1:

create table #scale (     name varchar(10),     [count] int )  insert #scale values     ('a', 19),     ('b', 1),     ('c', 25),     ('d', 100),     ('e', 29),     ('f', 60)   select name, [count],       ceiling([count] * 10.0 / (select max([count])  - min([count]) + 1 #scale)) [scale] #scale 

results:

| name | count | scale | |------|-------|-------| |    |    19 |     2 | |    b |     1 |     1 | |    c |    25 |     3 | |    d |   100 |    10 | |    e |    29 |     3 | |    f |    60 |     6 | 

this gets answer 60 becomes 7, hence 100 11:

select name, [count],       ceiling([count] * 10.0 / (select max([count])  - min([count]) #scale)) [scale] #scale