Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1972
  • Last Modified:

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;

  • 2
1 Solution
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;


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now