I'm essentially trying to work out which percentage value (e.g. 1 to 100) a range of numbers fall into.
For example, if I have a simple table of 500 records:
declare @temp table (id int, percentage int)
insert @temp (id)
select Number
from master..spt_values
where type = 'p'
and number between 1 and 500
select * from @temp
Then I'd like to work out that the first 1% of the records (i.e. those with id 1 to 5) will be be percentage number 1, the second 1% of records (i.e. those with id 6 to 10) will be number 2 etc.
I'm seen that some of the more recent versions on sql server have some percentage based functions like PERCENTILE and PERCENT_RANK which I think might have been a possibility, however I'm using SQL Server 2005 so not sure if my options are limited?
Is there an easy way to do this that I'm missing?
Thanks!
Aucun commentaire:
Enregistrer un commentaire