SQL Server: Count how big a column value is compared to the others

Question

I have table column filled with float values e.g.:

   id-Values
   1- 0.19230769230769232
   2- 8.4848484848484854E
   3- 0.10823529411764705
   4- 0.05459770114942529
...

I would like to know: is there a SQL function that allows me to return a percentage of the selected row position compared to the others?

For example: I want to know if there is an easy way to check if the row 4 is in the TOP 10%. Or if the row 2 is in the LAST 10% (order by values).

I know it's not possible to do a SELECT TOP 10% or a SELECT LAST 10% with SQL Server but it's just to give an example of what I want to do.

@Solution:

declare @a int
declare @b int
declare @values float

select @values = values from test where id <= 2

select @a = count(*) from test where values <= @values
select @b = count(*) from test 

select cast( cast(@a as float) / cast(@b as float) as float) * 100 as percentage

Answer 1

Check if following code help you.

declare @a int
declare @b int

select @a = count(*) from Foo where FooId <= 2
select @b = count(*) from Foo 


select cast( cast(@a as float) / cast(@b as float) as float) * 100 as percentage

Answer 2

Here's one way to do it. Based on the sample data set

CREATE TABLE Test (Id int not null, Data float not null)
insert Test values (1, 0.19230769230769232)
insert Test values (2, 8.4848484848484854E) 
insert Test values (3, 0.10823529411764705)  
insert Test values (4, 0.05459770114942529)

this will return the something like the percentage that you're looking for, based on the desired Id value as set in @Id:

DECLARE @Id int
SET @Id = 2

SELECT
  Test.*, 100 * xx.Position / (select count(*) from Test) PercentagePosition
 from Test
  inner join (select Id, row_number() over (order by Data) / 1.0 Position from Test) xx
   on xx.Id = Test.Id
 where Test.Id = @Id

I don't much like this, as it requires two table scans. Shortcuts might be devised, depending on what else the application needs to do.

Answer 3

Ok, this should be a SQL 2000 compatible version. Based on the same table structure as my prior answer:

DECLARE
  @Id    int
 ,@Data  float

SET @Id = 3

SELECT @Data = Data
 from Test
 where Id = @Id

SELECT (sum(case when Data < @Data then 1.0 else 0.0 end) + 1) / count(*)
 from Test

Assuming an index on Id, there's now only 1 table scan. In case of duplicate values, this will select the position based on the first occurance. Mess around with that +1; without it, the first value will get you 0%, with it, with four rows you'd get 25% -- so what is right for your application? Also, if the table is empty, you'll get a divide by zero error, so you'll need to handle that as appropriate to your application.