Link to home
Start Free TrialLog in
Avatar of pantoner
pantoner

asked on

SQL query percentile

Below is my query which ranks a value.  How do I make it return the percentile. I am using Access 2003.

SELECT tblA.RC1, tblA.RC2, tblA.RC3, tblA.[4], (Select Count (*) from tbl19complete Where Tbl19complete.[4] >[tblA].[4] And Tbl19complete.RC1=tblA.RC1 And Tbl19complete.RC2=tblA.RC2  And Tbl19complete.RC3=tblA.RC3)+1 AS Rank
FROM Tbl19Complete AS tblA;


ASKER CERTIFIED SOLUTION
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Markus Fischer
Markus Fischer
Flag of Switzerland image
Perhaps you just want this, in fact? (The top part is a rewrite of your own query.)

SELECT
  RC1, RC2, RC3, [4],
  (
    Select Count (*)
    From tbl19complete
    Where [4] >A.[4] And RC1=A.RC1 And RC2=A.RC2  And RC3=A.RC3
  )+1 AS Rank,
  (Rank-1) / (
    Select Count (*)
    From tbl19complete
    Where RC1=A.RC1 And RC2=A.RC2  And RC3=A.RC3
  ) AS Position
FROM Tbl19Complete A;

(°v°)