Find no.of position in column

[Senthilkumar]

Dear team,

I'm using a SQL query to get the number of positions based on the value of the field.

SQL Query is 

For BPosition:
$rnk = "SELECT Average, Emp_Name, FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC )  FROM mark WHERE Branch = '$Branch' )) AS rank FROM mark WHERE Emp_Name = '$Emp_Name' AND Year = '$Year' ORDER BY Average DESC ";
 $rest = mysqli_query($conn,$rnk);
 $row5 = mysqli_fetch_assoc($rest);
 $Rank= $row5['rank'];


For NPosition:
$rnk = "SELECT Average, Emp_Name, FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC )  FROM mark )) AS rank FROM mark WHERE Branch = '$Branch' AND Emp_Name = '$Emp_Name' AND Year = '$Year' ORDER BY Average DESC ";
$rest = mysqli_query($conn,$rnk);
$row5 = mysqli_fetch_assoc($rest);
$All= $row5['rank'];

SQL table  "mark" is

The first code is for the column BPosition. The position of the column average inside his own branch will be found by this code after it filters the branch.
Nposition is the second code. This code will locate the average column's position across all branches.
When I use the aforementioned code, it will determine the average column's location depending on the first digit of the inputted integer. Find the Nposition column's outcome. The score is 9 for the first row. It should actually be 3. Nposition, however, displays 1.
Can somebody tell me where the error is in my code?

Edited January 30, 2023 by Senthilkumar

[Barand]

I get results 1 and 3, which look right.

DATA
 

+---------+----------+--------+--------+------+---------+
| mark_id | emp_name | emp_no | branch | year | average |
+---------+----------+--------+--------+------+---------+
|       1 | Curly    | 1      | A      | 2023 |    9.00 |
|       2 | Larry    | 2      | B      | 2023 |   75.00 |
|       3 | Mo       | 3      | C      | 2023 |   66.67 |
+---------+----------+--------+--------+------+---------+

BPOSITION

SELECT Average
     , Emp_Name
     , FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC )  
                                 FROM mark
                                 WHERE Branch = 'A'
                                 )) AS rank
FROM mark
WHERE Emp_Name = 'Curly'
      AND Year = '2023'
ORDER BY Average DESC;
+---------+----------+------+
| Average | Emp_Name | rank |
+---------+----------+------+
|    9.00 | Curly    |    1 |
+---------+----------+------+

NPOSITION

SELECT Average
     , Emp_Name
     , FIND_IN_SET( Average, ( SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC )  FROM mark )) AS rank
FROM mark
WHERE Branch = 'A'
      AND Emp_Name = 'Curly'
      AND Year = '2023'
ORDER BY Average DESC;
+---------+----------+------+
| Average | Emp_Name | rank |
+---------+----------+------+
|    9.00 | Curly    |    3 |
+---------+----------+------+

PS

I don't think you should be using DISTINCT in your GROUP_CONCAT.

If the 3 values were 9.00, 75.00, 75.00 then, with your DISTINCT it would be ranked 2 whereas it should still be 3.

[Senthilkumar]

Mr.Barand,

Thanks for your reply, 

For me, the output of your code above still displays 1 on NPOSITION.

[Barand]

What do get if you run the subquery on its own?

SELECT GROUP_CONCAT(DISTINCT Average ORDER BY Average DESC )  FROM mark;

I suspect that your "average" column is varchar instead of a numeric type. (in my table it's DECIMAL(8,2) so that it sorts correctly.

[Senthilkumar]

Mr.Barand,

You are right. In my table, the "Average" column is in Varchar. No i changed it to decimal format and it is shorts correctly.

Thanks for your immediate support