CODEWITHSUNDEEP
mysql
Sushant Kumar
Sushant Kumar

Reputation: 3

Order by with where IN operator

How to make a query to mysql database to sort the results in descending order with condition that if a particular item present in list comes first. For example I have a table of some post 'post_feeds' article which is mapped with category in category map table 'post_category_mapping'. I try to retrieve all posts from table 'post_feeds' in sorted order but with condition that if category_id present in list come first.

I try to do this by this query :

SELECT DISTINCT post_category_mapping.post_id,
                posts_feeds.post_title
FROM post_category_mapping
INNER JOIN posts_feeds
    ON post_category_mapping.post_id = posts_feeds.post_id
ORDER BY CASE WHEN post_category_mapping.category_id IN (1,6)
              THEN posts_feeds.id END DESC,
         posts_feeds.id DESC"

Upvotes: 0

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

I think your query is close. To make the CASE trick work when ordering, you can assign a 0 for matching category IDs, and a 1 for non matching IDs:

SELECT DISTINCT t1.post_id,
                t2.post_title
FROM post_category_mapping t1
INNER JOIN posts_feeds t2
    ON t1.post_id = t2.post_id
ORDER BY CASE WHEN t1.category_id IN (1,6) THEN 0 ELSE 1 END,
         t2.id DESC

This would place all records having a category_id of 1 or 6 before all records not having these values. And within each matching/non matching block, the records would be ordered descending by posts_feed.id.

Upvotes: 1

Related Questions