Solved

# SQL query percentile

Posted on 2007-10-19
1,966 Views
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;

0
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
.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

LVL 58

Expert Comment

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

## Featured Post

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 (http://www.enterprise.efax.com), 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 (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…