Hi guys ,
I dont know if anyone can help this … even if it’s not 100% joomla …
i want to know if it’s possible to order by then group by in the same query ?
i have 2 tables (jos_content and jos_categories) with a complex sql :
$query = 'SELECT a.*,' .
' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,'.
' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug'.
' FROM #__content AS a' .
' INNER JOIN #__categories AS cc ON cc.id = a.catid' .
' INNER JOIN #__sections AS s ON s.id = a.sectionid' .
' WHERE a.state = 1 ' .
($noauth ? ' AND a.access <= ' .(int) $aid. ' AND cc.access <= ' .(int) $aid. ' AND s.access <= ' .(int) $aid : '').
' AND (a.publish_up = '.$db->Quote($nullDate).' OR a.publish_up <= '.$db->Quote($now).' ) ' .
' AND (a.publish_down = '.$db->Quote($nullDate).' OR a.publish_down >= '.$db->Quote($now).' )' .
' AND cc.section = '.(int) $secid .
' AND cc.published = 1' .
' AND s.published = 1' .
' ORDER BY a.catid ASC , a.created DESC' ;
// ' GROUP BY a.catid' ;
i want to get a list of articles grouped by category id (sorted by last date)
i also tried to use the keyword “DISTINCT (a.catid)” but it dident work , i think distinct will compare the whole rows instead of a specific column .
any idea how i could accomplish this ?