Mysqlde ranking ile ilgili bir fonksiyon yazılmamış ne yazık ki o nedenle ben burada bir örnek sunuyorum.
Aşağıdaki örnek shareyourmusic.net’in müzik tablosu üzerinden verilmiştir.
MUSIC TABLOSU:
DROP TABLE IF EXISTS `shareyourmusic`.`music`;
CREATE TABLE `shareyourmusic`.`music` (
`MusicID` int(11) NOT NULL auto_increment,
`Restriction` int(11) NOT NULL default '0',
`ID` int(11) default '0',
`title` varchar(60) collate utf8_turkish_ci default NULL,
`artist` varchar(32) collate utf8_turkish_ci default NULL,
`MusicType` enum('rock','pop','metal','rap','hiphop','classical','alternative','techno','other','reggae','instrumental','jazz','blues') collate utf8_turkish_ci NOT NULL default 'rock',
`hits` int(11) default '0',
`ranking` int(11) NOT NULL default '0',
`nrank` int(11) NOT NULL default '0',
`filetype` varchar(5) collate utf8_turkish_ci default NULL,
`filelocation` varchar(200) collate utf8_turkish_ci NOT NULL default '',
`filesize` int(11) NOT NULL default '0',
`permission` enum('listen','download','both') collate utf8_turkish_ci NOT NULL default 'listen',
`Lyrics` text collate utf8_turkish_ci,
PRIMARY KEY (`MusicID`)
) ENGINE=MyISAM AUTO_INCREMENT=339 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
MYRANK FONKSİYONU(MYSQL Command Line veya MYSQL Query Browser İçin):
DELIMITER $$
DROP FUNCTION IF EXISTS `shareyourmusic`.`MYRANK` $$
CREATE FUNCTION `shareyourmusic`.`MYRANK` (ptitle CHAR(50)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE RANKING INT;
SELECT COUNT(m2.musicID) INTO RANKING
FROM music m1,music m2 WHERE m1.hits <= m2.hits AND m1.title=ptitle
GROUP BY m1.id;
RETURN RANKING;
END $$
DELIMITER ;
PHPMyAdmin Kullananlar İçin: SQL Script Yazılan Bölümde DELIMETER’ı $$ olarak girdikten sonra aşağıdakini uyguluyorsunuz.
DROP FUNCTION IF EXISTS `shareyourmusic`.`MYRANK` $$
CREATE FUNCTION `shareyourmusic`.`MYRANK` (ptitle CHAR(50)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE RANKING INT;
SELECT COUNT(m2.musicID) INTO RANKING
FROM music m1,music m2 WHERE m1.hits <= m2.hits AND m1.title=ptitle
GROUP BY m1.id;
RETURN RANKING;
END $$
Fonksiyonu Çağırma Örneği:
SELECT title,artist,MYRANK(title) AS rank FROM music ORDER BY HITS DESC
