Use order by DESC and ASC with Group By

Question

Hello everybody, Here is my sql statement

 SELECT DISTINCT article_categories.category_id, 
   article_id,article_title,article_intro,article_content,
   category_name_ar 
 FROM articles,article_categories 
 WHERE articles.category_id=article_categories.category_id 
   AND article_type='admin' 
   AND category_case='active' 
 GROUP BY article_categories.category_id 
 ORDER BY article_categories.category_id ASC,
          article_date,article_time DESC 
 LIMIT 10

I would like to retrieve the first DISTINCT 10 categories. In addition I would like to retrieve the equivalent articles where the articles.category_id equals article_categories.category_id. So far the result is good but what I am looking for is to order the result by category_id and in the same time order the result by the article_date,article_time DESC.

Any assistance will be appreciated in advance

Answer 1

SELECT distinct c.category_id,...
FROM articles a,article_categories c WHERE a.category_id=c.category_id
AND ...
AND a.article_id in
(SELECT max(a1.article_id)
FROM articles a1  WHERE a1.category_id=c.category_id ) ORDER BY c.category_id ASC

Please replace ... with additional fields and conditions you need.

Answer 2

only add desc like

ORDER BY article_categories.category_id ASC,
          article_date DESC,article_time DESC 

Answer 3

I got it

SELECT DISTINCT article_categories.category_id, article_id,article_title,article_intro,article_content, category_name_ar FROM articles,article_categories WHERE articles.category_id=article_categories.category_id AND article_type='admin' AND category_case='active' ORDER BY article_categories.category_id ASC, article_date desc,article_time DESC LIMIT 10

Thanks all of your advises, it helped alot