CODEWITHSUNDEEP
sqlsql-server
user2096512
user2096512

Reputation: 469

SQL Find which percentile

I'm trying to find which percentile of the finishing position of a runner in a race, eventually I want to use it in a query like this: 

Percentile(runners/placing) = "Percentile of the runner who came in 3rd place out of 10 runners = 27" 
UPDATE Scores SET ScoreFinal = (IIF(Percentile >= 50, Score * 2, Score * 3))

I'm currently using a peice of VB code to find the percentile, not sure ho to do it in SQL:

    Public Shared Function Get_Percentile(ByVal runners As Integer, position As Integer) As Double

    Dim smplTstScores(runners) As Integer

    For i = 1 To runners
        smplTstScores(i) = i
    Next

    Dim smplData As IEnumerable(Of Integer) = smplTstScores.OrderBy(Function(n) n)
    Dim scoresLess As Integer = smplData.Count(Function(n) n < position)
    Dim p As Double = Math.Round(scoresLess / smplTstScores.Length * 100) 'the percentile

    Return p

End Function

Upvotes: 0

Views: 195

Answers (2)

Lee Marshall
Lee Marshall

Reputation: 1

Edited to add: I understand the goal here is to execute an UPDATE statement that awards greater points to competitors above the 50th percentile, that is, more or less, above the median, than to those below it. If you actually want the percentiles in your final result, indeed you'll need to do something more.

I originally didn't see the tag for SQL Server. SQL Server definitely isn't my first language; but I hope you find these pointers useful. SQL Server offers the TOP clause with the fine additions PERCENT and WITH TIES. These could be really handy for you! https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15 If you really do require percentiles, PERCENT_RANK looks like the way to go! https://learn.microsoft.com/en-us/sql/t-sql/functions/percent-rank-transact-sql?view=sql-server-ver15 This past answer looks like the simplest median: Function to Calculate Median in SQL Server

Original answer: To solve your problem as stated, you don't need the percentile, just the median. A previous answer gives you ways to get that: Simple way to calculate median with MySQL

Depending on where you're coming from and going, some SQL's SELECT ... LIMIT clauses also allow you to specify an offset, meaning you could pick a single record out of the middle. https://dev.mysql.com/doc/refman/8.0/en/select.html (you'll have to search for "LIMIT", sorry.)

In most cases, I'd argue the nice distinction about calculating median for an even number of values by averaging the middle two is not worth the effort. Maybe your use case is scoring a larger competition where fairness demands exactitude. In that case, though, I think the question you have to answer is what happens if there's a tie at the 50pctl. For example, ten runners in the race and all cross the finish line abreast!

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

So basically 3 / 10? In sql you have to be careful as this will always return 0 because of integer division. Simply multiple either value by 1.0

See this example to see what I mean.

declare @Position int = 3
    , @Runners int = 10

select @Position / @Runners --integer math will use whole numbers
    , @Position / (@Runners * 1.0) --multiplying by 1.0 forces this to decimal division.

I would think that 3rd place should be 70%. So to accomplish that simply use:

1 - (@Position / (@Runners * 1.0))

Upvotes: 1

Related Questions