select
T1.Field1
, PERCENT_RANK()
over( order by Field1 asc ) as PercentRank
, cast(
round(
PERCENT_RANK()
over( order by Field1 asc )
* 100
, 0
)
as int
) as PercentRankInt
from
(
select 1 as Field1
union all select 1
union all select 1
union all select 2
union all select 2
union all select 3
union all select 4
--union all select 8
) as T1
order by
Field1
;
PercentRankInt calculates the percentile each value occupies in relation to all other values in the column, as an integer number, ready to be used for quick filters, thus enabling the exclusion of, for example, top and bottom deciles, quintiles, etc.