Leaderboard

Here's my function for ordinal suffices function ordSuffix($n) { $str = "$n"; $t = $n > 9 ? substr($str,-2,1) : 0; $u = substr($str,-1); if ($t==1) return $str . 'th'; else switch ($u) { case 1: return $str . 'st'; case 2: return $str . 'nd'; case 3: return $str . 'rd'; default: return $str . 'th'; } } echo ordSuffix(1).'<br>'; // 1st echo ordSuffix(11).'<br>'; // 11th echo ordSuffix(101).'<br>'; // 101st echo ordSuffix(2).'<br>'; // 2nd echo ordSuffix(3).'<br>'; // 3rd echo ordSuffix(8).'<br>'; // 8th

Try this SELECT yearid , semesterid , subjectid , regno , rank , total , grade , comment FROM ( SELECT yearid , semesterid , subjectid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, subjectid) as yss , regno , total FROM subject_position ORDER BY yearid, semesterid, subjectid, total DESC -- LIMIT 18446744073709551615 -- MariaDB requires this line uncommented * ) sorted JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init ) ranked JOIN grade ON total BETWEEN grade.lomark AND grade.himark; (I invented my own grades) mysql> select * from grade; +----+-------+--------+--------+-------------+ | id | grade | lomark | himark | comment | +----+-------+--------+--------+-------------+ | 1 | A* | 91 | 100 | Distinction | | 2 | A | 80 | 90 | Excellent | | 3 | B1 | 75 | 79 | V Good | | 4 | B2 | 70 | 74 | V Good | | 5 | C1 | 68 | 69 | Good | | 6 | C2 | 66 | 67 | Good | | 7 | C3 | 64 | 65 | Good | | 8 | C4 | 61 | 63 | Good | | 9 | D1 | 59 | 60 | Pass | | 10 | D2 | 57 | 58 | Pass | | 11 | D3 | 55 | 56 | Pass | | 12 | D4 | 53 | 54 | Pass | | 13 | D5 | 51 | 52 | Pass | | 14 | D6 | 49 | 50 | Pass | | 15 | D7 | 40 | 48 | Pass | | 16 | E | 21 | 39 | Poor | | 17 | F | 0 | 20 | Fail | +----+-------+--------+--------+-------------+ Query results... +--------+------------+-----------+-------+------+-------+-------+-------------+ | yearid | semesterid | subjectid | regno | rank | total | grade | comment | +--------+------------+-----------+-------+------+-------+-------+-------------+ | 1 | 1 | 1 | 6073 | 1 | 73 | B2 | V Good | | 1 | 1 | 1 | 4663 | 2 | 72 | B2 | V Good | | 1 | 1 | 2 | 6073 | 1 | 61 | C4 | Good | | 1 | 1 | 2 | 4663 | 2 | 47 | D7 | Pass | | 1 | 1 | 3 | 4663 | 1 | 82 | A | Excellent | | 1 | 1 | 3 | 6073 | 2 | 61 | C4 | Good | | 1 | 1 | 4 | 4663 | 1 | 99 | A* | Distinction | | 1 | 1 | 4 | 6073 | 2 | 95 | A* | Distinction | | 1 | 1 | 5 | 6073 | 1 | 100 | A* | Distinction | | 1 | 1 | 5 | 4663 | 2 | 70 | B2 | V Good | | 1 | 1 | 6 | 4663 | 1 | 69 | C1 | Good | | 1 | 1 | 6 | 6073 | 2 | 67 | C2 | Good | | 1 | 1 | 7 | 6073 | 1 | 80 | A | Excellent | | 1 | 1 | 7 | 4663 | 2 | 77 | B1 | V Good | | 1 | 1 | 8 | 6073 | 2 | 77 | B1 | V Good | | 1 | 1 | 8 | 4663 | 2 | 58 | D2 | Pass | | 1 | 1 | 9 | 4663 | 1 | 96 | A* | Distinction | | 1 | 1 | 9 | 6073 | 1 | 96 | A* | Distinction | | 1 | 1 | 10 | 4663 | 1 | 78 | B1 | V Good | | 1 | 1 | 10 | 6073 | 2 | 77 | B1 | V Good | | 1 | 1 | 11 | 6073 | 1 | 88 | A | Excellent | | 1 | 1 | 11 | 4663 | 2 | 48 | D7 | Pass | | 1 | 1 | 12 | 6073 | 1 | 94 | A* | Distinction | | 1 | 1 | 12 | 4663 | 2 | 69 | C1 | Good | | 1 | 2 | 1 | 6073 | 1 | 70 | B2 | V Good | | 1 | 2 | 1 | 4663 | 2 | 28 | E | Poor | | 1 | 2 | 2 | 4663 | 1 | 68 | C1 | Good | | 1 | 2 | 2 | 6073 | 2 | 59 | D1 | Pass | | 1 | 2 | 3 | 6073 | 1 | 70 | B2 | V Good | | 1 | 2 | 3 | 4663 | 2 | 68 | C1 | Good | | 1 | 2 | 4 | 4663 | 1 | 81 | A | Excellent | | 1 | 2 | 4 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 5 | 4663 | 1 | 84 | A | Excellent | | 1 | 2 | 5 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 6 | 6073 | 2 | 72 | B2 | V Good | | 1 | 2 | 6 | 4663 | 2 | 58 | D2 | Pass | | 1 | 2 | 7 | 4663 | 1 | 71 | B2 | V Good | | 1 | 2 | 7 | 6073 | 2 | 70 | B2 | V Good | | 1 | 2 | 8 | 6073 | 1 | 55 | D3 | Pass | | 1 | 2 | 8 | 4663 | 2 | 48 | D7 | Pass | | 1 | 2 | 9 | 4663 | 1 | 66 | C2 | Good | | 1 | 2 | 9 | 6073 | 2 | 51 | D5 | Pass | | 1 | 2 | 10 | 6073 | 1 | 58 | D2 | Pass | | 1 | 2 | 10 | 4663 | 2 | 37 | E | Poor | | 1 | 2 | 11 | 6073 | 1 | 59 | D1 | Pass | | 1 | 2 | 11 | 4663 | 2 | 57 | D2 | Pass | | 1 | 2 | 12 | 6073 | 1 | 69 | C1 | Good | | 1 | 2 | 12 | 4663 | 2 | 67 | C2 | Good | | 1 | 3 | 1 | 4663 | 1 | 94 | A* | Distinction | | 1 | 3 | 1 | 6073 | 2 | 82 | A | Excellent | | 1 | 3 | 2 | 6073 | 1 | 76 | B1 | V Good | | 1 | 3 | 2 | 4663 | 2 | 69 | C1 | Good | | 1 | 3 | 3 | 6073 | 1 | 81 | A | Excellent | | 1 | 3 | 3 | 4663 | 2 | 63 | C4 | Good | | 1 | 3 | 4 | 4663 | 1 | 81 | A | Excellent | | 1 | 3 | 4 | 6073 | 2 | 77 | B1 | V Good | | 1 | 3 | 5 | 6073 | 1 | 83 | A | Excellent | | 1 | 3 | 5 | 4663 | 2 | 72 | B2 | V Good | | 1 | 3 | 6 | 6073 | 1 | 83 | A | Excellent | | 1 | 3 | 6 | 4663 | 2 | 78 | B1 | V Good | | 1 | 3 | 7 | 4663 | 1 | 77 | B1 | V Good | | 1 | 3 | 7 | 6073 | 2 | 75 | B1 | V Good | | 1 | 3 | 8 | 6073 | 1 | 82 | A | Excellent | | 1 | 3 | 8 | 4663 | 2 | 74 | B2 | V Good | | 1 | 3 | 9 | 6073 | 1 | 95 | A* | Distinction | | 1 | 3 | 9 | 4663 | 2 | 56 | D3 | Pass | | 1 | 3 | 10 | 4663 | 1 | 87 | A | Excellent | | 1 | 3 | 10 | 6073 | 2 | 79 | B1 | V Good | | 1 | 3 | 11 | 6073 | 1 | 71 | B2 | V Good | | 1 | 3 | 11 | 4663 | 2 | 70 | B2 | V Good | | 1 | 3 | 12 | 6073 | 1 | 90 | A | Excellent | | 1 | 3 | 12 | 4663 | 2 | 82 | A | Excellent | +--------+------------+-----------+-------+------+-------+-------+-------------+ [edit] * The idiots at Maria maintain that as the subquery effectively creates a table, and tables are inherently unordered, then the ORDER BY clause should be ignored. The rest of the world think this is a resultset and there can be ordered. The LIMIT 264-1 forces it to use an ordered temporary table.