SELECT person.pid
,CONCAT_WS('-',substring(idcard,1,1),substring(idcard,2,4),substring(idcard,6,5),substring(idcard,11,2),substring(idcard,13,1))AS idcardd
,CONCAT(ctitle.titlename,' ',person.fname,' ',person.lname) AS pname
,CONCAT(DATE_FORMAT(person.birth,'%d-%m-'),DATE_FORMAT(person.birth,'%Y')+543) AS tbirth
,YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) AS age
,person.hnomoi
,SUBSTRING(house.villcode,7,2) AS villno
,chroniccode
FROM person
LEFT JOIN ctitle ON person.prename = ctitle.titlecode
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
INNER JOIN personchronic ON person.pid=personchronic.pid AND person.pcucodeperson=personchronic.pcucodeperson
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
WHERE persondeath.pid IS NOT NULL
#AND getAgeYearNum (birth,CURDATE()) >= 60
AND person.typelive in ('0','1','3')
AND right((house.villcode),2) != 00
GROUP BY personchronic.pid
ORDER BY age,villno,hnomoi*1;