SQL query percentile

Posted on 2007-10-19
Last Modified: 2008-03-18
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;

Question by:pantoner
    LVL 58

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

    LVL 58

    Expert Comment

    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 Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now