Se puede simplificar esta sentencia?...

[jurena]

talle,
La de los 10 últimos. He usado los datos y nombres que tengo yo en mi base. No he puesto date porque creo que es una palabra reservada y no debe usarse para campo.

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks, t1.totcom, dl.STATUS
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl.id, COUNT( * ) totcom
FROM `dir_links` dl
INNER JOIN dir_link_comments dlc ON dlc.id_link = dl.id
WHERE dl.id_category !=333
AND dl.STATUS = 'active'
GROUP BY dl.id
ORDER BY dl.fecha_de_alta DESC 
LIMIT 10 
)t1 ON dl.id = t1.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id
GROUP BY dlc.id_link
ORDER BY dl.fecha_de_alta DESC
 

Por el rank

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, COUNT( dlc.id_link ) totallinks, t2.totcom
FROM dir_links dl INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (  SELECT dl.id FROM `dir_links` dl WHERE dl.id_category !=333 AND dl.STATUS = 'active' ORDER BY rank DESC LIMIT 10 )t1 ON dl.id = t1.id
INNER JOIN (SELECT dlco.id_link AS id, COUNT(dlco.id_link) totcom FROM dir_link_comments dlco GROUP BY dlco.id_link)T2 ON T1.id = t2.id
INNER JOIN dir_link_clicks dlc ON dlc.id_link = dl.id GROUP BY dlc.id_link ORDER BY dl.rank DESC , totallinks DESC
 

Cuando es por clicks

Código sql:

SELECT dl.title, dl.location, dl.url, dl.rank, dl.fecha_de_alta, dl.description, dc.path, dl.STATUS, t1.tot AS totallinks, t2.totcom
FROM dir_links dl
INNER JOIN dir_categories dc ON dl.id_category = dc.id
INNER JOIN (
 
SELECT dl2.id, COUNT( dlc.id_link ) AS tot
FROM `dir_links` dl2
INNER JOIN dir_link_clicks dlc ON dl2.id = dlc.id_link
WHERE dl2.id_category !=333
AND dl2.STATUS = 'active'
GROUP BY dlc.id_link
ORDER BY tot DESC 
)t1 ON dl.id = t1.id
INNER JOIN (
 
SELECT dlco.id_link AS id, COUNT( dlco.id_link ) AS totcom
FROM dir_link_comments dlco
GROUP BY dlco.id_link
)t2 ON t1.id = t2.id
ORDER BY totallinks DESC , dl.rank DESC