Rankings based on multiple field values for table that is filtered

This topic is locked

Rankings based on multiple field values for table that is filtered	12/30/2021 8:45:30 PM
PHPRunner General questions
D Dynamiccomp author I've been playing around with this, but can't seem to get the results I am expecting. I have a table called 'scoring' which on the list page is filtered by the 'division' I need to rank the date in ascending order from smallest total to largest total, which I am currently doing with the following SQL query: FIND_IN_SET(`Final Total`, ( SELECT GROUP_CONCAT( DISTINCT `Final Total` ORDER BY `Final Total` ASC ) FROM Scoring)) AS `Final Rank`, However, that is ranking all of the records in the entire table, instead of ranking based on the 'division name,' so is there a way to do this, since its in the same table, or would the data have to be placed in separate tables to rank correctly? Also as a side note to this question, the ranking sorts perfectly as long as there are only records 1 - 10, but when it goes past 10 they are sorted like (1.11.2.13,3...) I have tried changing the field type to int from varchar, but that didn't help the sort. Any help would be greatly appreciated.


D	Dynamiccomp author 12/30/2021
	So I found a solution online which partially works, but if I use it to do both rankings, the rankings are messed up. I need to do the inital rank which is the field 'rank' based on the field 'total' and then the second ranking that I want to perform is the field 'final rank' based on the 'final total' field. Here is the code I found to use: (CASE Division WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := Division END ) + 1 AS `Final Rank`, then it said to add this code after the 'From database line' `(SELECT @curRow := 0, @curType := '') r` At this point it displays correctly, however if I were to add the following to the sql query both rankings get messed up `(CASE Division WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := Division END ) + 1 AS Rank,` I am sure that I am missng something, but not sure what it is.