mysql最大用户是谁_mysql – 按最大ID和点排名用户但是(排名错误)

我试图得到最高点行和排名和选择查询没有rownum = rownum 1.我已经尝试了下面的查询,但我遗漏的东西也链接http://sqlfiddle.com/#!2/fd7897/7.

我正在寻找每个接收器的答案,最后一个条目也是排名最高的入口：我非常感谢任何帮助.谢谢你.

像这样的东西：

(‘2′,’4′,’测试……’,’2011-08-21 14:13:19′,’40’,’009′)— rank1

(‘4′,’2′,’30’,’003′)—- rank2

(‘1′,’3′,’2011-08-21 14:12:19′,’20’,’005′)—- rank3

查询到目前为止：

SELECT *,(select count(*)

from tblA u2

where u2.points > u.points or

u2.points = u.points and u2.id <= u.id

) as rank

FROM (SELECT u.receiver,MAX(u.id) AS id

FROM tblA u

GROUP BY u.receiver

) subset JOIN

tblA u

ON subset.receiver = u.receiver AND subset.id = u.id order by rank;

表：

CREATE TABLE if not exists tblA

(

id int(11) NOT NULL auto_increment,sender varchar(255),receiver varchar(255),msg varchar(255),date timestamp,points int(255),refno varchar(255),PRIMARY KEY (id)

);

CREATE TABLE if not exists tblB

(

id int(11) NOT NULL auto_increment,sno varchar(255),name varchar(255),PRIMARY KEY (id)

);

CREATE TABLE if not exists tblC

(

id int(11) NOT NULL auto_increment,data varchar(255),extrarefno varchar(255),PRIMARY KEY (id)

);

INSERT INTO tblA (sender,receiver,msg,date,points,refno ) VALUES

('1','2','buzz ...','2011-08-21 14:11:09','10','001'),('1','test ...','2011-08-21 14:12:19','20','002'),('4','2011-08-21 14:13:19','30','003'),'3','004'),'005'),'4','006'),'007'),('3','008'),('2','40','009');

INSERT INTO tblB (sno,name ) VALUES

('1','Aa'),'Bb'),'Cc'),'Dd'),('5','Ee'),('6','Ff'),('7','Gg'),('8','Hh');

INSERT INTO tblC (data,refno,extrarefno ) VALUES

('data1','001','101'),('data2','002','102'),('data3','003','103'),('data4','004',('data5','005',('data6','006',('data7','007',('data8','008',('data9','009','101');