# Find no.of position in column

Go to solution Solved by Barand,

## Recommended Posts

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 by Senthilkumar
##### Share on other sites

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.

##### Share on other sites

Mr.Barand,

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

##### Share on other sites

• Solution

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.

##### Share on other sites

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

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.