Due to my lack of expertise with MySQL I have two rather basic questions. I use this query to give me the results I need, but I have to adjust the bold figures manually: SELECT computer_ID, 100 - ( nr -1) * (100/157), 100 - (nr_rpi - 1)*(100/157), ... FROM xyz WHERE Version LIKE 6.0 ORDER BY nr The problem is the number 157. Ideally this should read 'COUNT(*)-1' or 'MAX(nr)-1'. But somehow this only gives me a single row, not the complete list. What I need to do is change absolute rankings on different variables to a scale of 0 to 100. I have - in this example - rankings from 1 to 158 (1 being top) and I need to adjust them to a scale of 100 (100 being top) to 0. What do I need to change in this query? I need to generate the rankings used above and currently I use manual labor to create these, but obviously it is far from ideal. This is what I use to manually generate these ranking figures: First empty Benchmark_results. Then get rank for total time: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000 Now update rank for total time: UPDATE Personal_data, Benchmark_results SET Personal_data.nr=Benchmark_results.RankCS6 WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0 Now empty Benchmark_results again and repeat for crpi: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY crpi, total_time, cgain ASC LIMIT 0, 5000 Now update rank for crpi only: UPDATE Personal_data, Benchmark_results SET Personal_data.nr_rpi=Benchmark_results.nr_rpi WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0 Now repeat for Disk_io: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY Diskio, crpi, total_time, cgain ASC LIMIT 0, 5000 And update rank for Diskio: UPDATE Personal_data, Benchmark_results SET Personal_data.nr_disk=Benchmark_results.nr_disk WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0 Repeat for dvd: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY dvd, crpi, total_time, cgain ASC LIMIT 0, 5000 And update rank for dvd: UPDATE Personal_data, Benchmark_results SET Personal_data.nr_mpeg=Benchmark_results.nr_dvd WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0 Repeat for h264: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY h264, crpi, total_time, cgain ASC LIMIT 0, 5000 And update rank for h264: UPDATE Personal_data, Benchmark_results SET Personal_data.nr_h264=Benchmark_results.nr_h264 WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0 Now clean up again: Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID, Total, RPI, Gain) SELECT @rank:=@rank+1 AS Rank, @Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6, @Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi, @Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk, @Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd, @Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264, Ref_ID, Diskio+dvd+h264+IFNULL(MPE_On,MPE_Off) As Total, FORMAT((50.893*Diskio+158.333*dvd+77.027*h264+950*IFNULL(MPE_On,MPE_Off))/12138.0,4) As RPI, FORMAT(MPE_Off/IFNULL(MPE_On,MPE_Off),3) As Gain FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000 This of course is far from ideal, but the only way I figured out that works, but among you geniuses there must be someone who can help me optimize this workflow and I'm open to all suggestions.
... View more