I am using the query to find the rank of a user in a given test id, and this gives me rank for just one test at a time and I have to use foreach to get the test rank of all the test.
SELECT * , t.UserRank
FROM (
SELECT * , (
@rownum := @rownum +1
)UserRank
FROM User_Date_Table, (
SELECT @rownum :=0
)t
WHERE my_test_id = '$test_id'
ORDER BY test_score DESC
)t
WHERE user_id = '$my_user_id'
Can a query be generated which can give me user rank for all the test taken by user in single query as this will reduced the db hitting multiple times.
I have to find the all the ranks of user with Test_Type_Id = $my_test_type_id(say), joining a.id with b.my_test_id for a user with user_id = $my_user_id(say)
TABLE STRUCTURE
My_Test_Table (a)
id | name | Test_Type_Id
----------------------------------------------
1 | name_1 | 1
2 | name_2 | 1
3 | name_3 | 2
4 | name_4 | 1
5 | name_5 | 1
6 | name_6 | 2
7 | name_7 | 1
8 | name_8 | 2
9 | name_9 | 1
User_Date_Table (b)
id | my_test_id | user_id | test_score
---------------------------------------------------------
1 | 1 | 32 | 34
2 | 1 | 2 | 345
3 | 2 | 4 | 654
4 | 1 | 76 | 87
5 | 3 | 23 | 453
6 | 2 | 5 | 45
7 | 1 | 43 | 22
8 | 2 | 7 | 987
9 | 2 | 32 | 45
10 | 1 | 1 | 12
11 | 1 | 9 | 35
12 | 3 | 67 | 765
13 | 1 | 88 | 23
14 | 2 | 34 | 76
15 | 3 | 1 | 765
16 | 2 | 54 | 45
17 | 1 | 10 | 87
18 | 1 | 23 | 3
19 | 3 | 44 | 345
20 | 1 | 55 | 232
21 | 2 | 28 | 234
22 | 3 | 32 | 231