# Thread: Help Needed on Rank Percentile Calculation

1. Registered User
Join Date
Jun 2017
Posts
1

## Unanswered: Help Needed on Rank Percentile Calculation

I am a beginner of SQL and would be appreciate a help from anyone who can. I have created ms access query
SELECT R1.ID, R1.SampleID, R1.HARD, Count(*) AS Rank
FROM Rep_Stats AS R1, Rep_Stats AS R2
GROUP BY R1.ID, R1.SampleID, R1.HARD
ORDER BY R1.HARD;

which generates the following table;

ID SampleID HARD Rank
1995 EN0038966 0.11 1
1751 EN0040765 0.14 2
1957 EN0038189 0.14 3
1732 EN0035089 0.17 4
1888 EN0039363 0.19 5
2020 EN0031584 0.21 6

Now, I want to add a 5th column to the query where each rank value will be divided by the highest rank value (i.e. 6) multiplied by 100. I do not want to create the 5th column as Rank/6, because as the data increases, the highest rank will be greater than 6. I would be glad if anyone could help me use with query to extract the highest rank value (say Rh) and use it to divide all the rank values. Thus, the final query table should look like this;

ID SampleID HARD Rank PCTL
1995 EN0038966 0.11 1 (1/Rh)*100
1751 EN0040765 0.14 2 (2/Rh)*100
1957 EN0038189 0.14 3 (3/Rh)*100
1732 EN0035089 0.17 4 (4/Rh)*100
1888 EN0039363 0.19 5 (5/Rh)*100
2020 EN0031584 0.21 6 (6/Rh)*100

NOTE: Rh = Highest Rank Value

Last edited by rokine; 06-14-17 at 13:16.

2. Registered User
Join Date
Jan 2009
Location
Kerala, India
Posts
188
Try the Query with the following change of SQL:
Code:
```SELECT R1.ID, R1.SampleID, R1.HARD, Count(*) AS Rank, Rank/Dmax("Rank","QueryName")*100 AS PCTL
FROM Rep_Stats AS R1, Rep_Stats AS R2
GROUP BY R1.ID, R1.SampleID, R1.HARD
ORDER BY R1.HARD;```
Replace the 'QueryName' with the correct name of the Query.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•