CODEWITHSUNDEEP
mysql
Hassan
Hassan

Reputation: 41

Use order by DESC and ASC with Group By

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

Upvotes: 3

Views: 38091

Answers (3)

spbfox
spbfox

Reputation: 949

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.

Upvotes: 3

Hassan
Hassan

Reputation: 41

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

Upvotes: 1

Haim Evgi
Haim Evgi

Reputation: 125476

only add desc like

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

Upvotes: 0

Related Questions