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?
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
        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)
            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.)

Perhaps you just want this, in fact? (The top part is a rewrite of your own query.)

  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;

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.

All Courses

From novice to tech pro — start learning today.