# 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;

###### Who is Participating?

Commented:
I created a percentile function for Access in http:/Q_22723540.html which might be useful. For it to be fast, you need to consider indexes, especially if you call it from a query, generating several percentile calculations at once...

I post it again here because the first version was buggy. This incorporates the last bug fix at the end of the thread:

Function Percentile( _
Percent As Double, _
Expression As String, _
Source As String, _
Optional Criteria = Null)

Dim strSQL As String
Dim dblPosition As Double

Percentile = Null
strSQL = "SELECT " & Expression _
& " FROM " & Source _
& " WHERE " & Expression & " Is Not Null" _
& " AND " + Criteria _
& " ORDER BY " & Expression
With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If .RecordCount = 0 Then Exit Function
If Percent <= 0 Then Percentile = .Fields(0): Exit Function
.MoveLast
If Percent >= 1 Then Percentile = .Fields(0): Exit Function
dblPosition = (.RecordCount - 1) * Percent
.AbsolutePosition = Int(dblPosition)
Percentile = .Fields(0)
If .AbsolutePosition < dblPosition Then
Percentile = Percentile * (1 - dblPosition + .AbsolutePosition)
.MoveNext
Percentile = Percentile _
+ .Fields(0) * (1 - .AbsolutePosition + dblPosition)
End If
End With

End Function

If you want to show the percentile value for each record, you are probably better off with a VB loop updating a special field for that. It will be much faster. Open a recordset containing one set you want to rank in the correct order, move to the last record to populate the recordcount and then loop over all records, writing the property .PercentPosition into the field. (Tell me if you need help with that.)

Cheers!
(°v°)
0

Commented:
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°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.