I have a table with concatenated values within both rows, I am therefore uniquely retrieve ranking for each row in the tables.
UPDATE The other tables has been added to question
NamesTable
NID | Name |
1 | Mu |
2 | Ni |
3 | ices |
GroupTable
GID | GName |
1 | GroupA |
2 | GroupB |
3 | GroupC |
MainTable
| NID | Ages | Group |
| 1 | 84 | 1 |
| 2 | 64 | 1 |
| 3 | 78 | 1 |
| 1 | 63 | 2 |
| 2 | 25 | 2 |
| 3 | 87 | 2 |
| 1 | 43 | 3 |
| 2 | 62 | 3 |
| 3 | 37 | 3 |
Now the first Name is equated to the first age in the table, I am able to equate them using php and foreach statements, Now the problem is with the ranking of the ages per each group. I am ranking the names uniquely on each row or group.
Results which is expected
| Names | Ages | Group | Ranking |
| Mu,Ni,ices | 84,64,78 | 1 | 1,3,2 |
| Mu,Ni,ices | 63,25,87 | 2 | 2,3,1 |
| Mu,Ni,ices | 43,62,37 | 3 | 2,1,3 |
In my quest to solving this, I am using GROUP_CONCAT, and I have been able to come to this level in the below query
SELECT
GROUP_CONCAT(Names) NAMES,
GROUP_CONCAT(Ages) AGES,
GROUP_CONCAT(Group) GROUPS,
GROUP_CONCAT( FIND_IN_SET(Ages, (
SELECT
GROUP_CONCAT( Age ORDER BY Age DESC)
FROM (
SELECT
GROUP_CONCAT(Ages ORDER BY Ages DESC ) Age
FROM
`MainTable` s
GROUP by `Group`
) s
)
)) rank
FROM
`MainTable` c
GROUP by `Group`
This actually gives me the below results.
| Names | Ages | Group | Ranking |
| 1,2,3 | 84,64,78 | 1 | 7,9,8 |
| 1,2,3 | 63,25,87 | 2 | 5,6,4 |
| 1,2,3 | 43,62,37 | 3 | 2,1,3 |
The only problem is that the ranking Values increase from 1 to 9 instead of ranking each row uniquely. Its there any idea that can help me cross over and fix this? I would be grateful for your help. Thanks