stored procedures - How to manage ties in mysql -
i have tables
create table `autores` ( `id_autor` int(11) not null default '0', `login` varchar(60) not null default '', `password` varchar(64) not null default '', `correo` varchar(100) not null default '', `url` varchar(100) not null default ''); create table `noticias` ( `id` int(11) not null default '0', `autor_id` char(15) not null default '0', `fecha_pub` datetime not null default '0000-00-00 00:00:00', `contenido` longtext not null, `titulo` text not null, unique key `in1` (`id`), fulltext key `full` (`titulo`,`contenido`) ) engine=myisam auto_increment=3844 default charset=latin1; now i've created procedure calculates who's author has "articles" published.
create procedure masnoticiasmes(in mes int) begin declare empate int default 0; declare nombreautor varchar(30); declare cantidad, autor, autormax int; declare maximo, fin int default 0; declare nombreautor2,nombreempate char(30); declare empatado char(30); declare micursor cursor select count(id), autor_id noticias month(fecha_pub)=mes group autor_id; declare continue handler not found set fin=1; open micursor; repeat fetch micursor cantidad, autor; if fin=0 if cantidad>maximo set maximo=cantidad; set autormax=autor; set empate=0; end if; if cantidad=maximo , autormax != autor set empatado=autor; set empate=empate+1; end if; end if; until fin end repeat; select login autores join noticias on noticias.autor_id=autores.id_autor cantidad in (maximo) nombreempate; close micursor; select login autores join noticias on noticias.autor_id=autores.id_autor autores.id_autor=empatado nombreempate; select concat ( "el que mas ha publicado es el: ", nombreautor2," con ", maximo, " noticias ", " empatados ", empate,nombreempate) "resultado"; end// now problem when tie happens , have say
the published author x (because of way cursor works picks first published if there's tie) says there x ties , author has tie x , problem published author variable , , when there's tie between 2 works fine , selecting table name of author id author id. if there more 2 authors , have store name in variable , 1 variable every author ties not doable , how it?
Comments
Post a Comment