CODEWITHSUNDEEP
mysqlsqlsql-order-by
Anurag Peshne
Anurag Peshne

Reputation: 1547

Conditional ordering with priority to a column

I have a table with 4 columns:

  1. ProductID
  2. IsSponsored
  3. Rank
  4. PopularityScore

I want to get top 20 Products ordered by Rank and PopularityScore. However Rank is considered only if IsSponsored = 1. I have tried using CASE in ORDER BY clause. Something like:

SELECT ProductID
FROM ProductTable 
ORDER BY
CASE WHEN IsSponsored = 1 THEN RANK END ASC,
CASE WHEN IsSponsored = 0 THEN PopularityScore END DESC
LIMIT 20;

The Problem I'm facing is, since number of rows with IsSponsored = 0 >> number of rows with IsSponsored = 1 and the limit 20, MySQL fetches rows with IsSponsored = 0 ordered by PopularityScore. What I want is get to all Sponsored Products ordered by Ranks and then supplemented by non sponsored Products ordered by Popularity score.

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Try conditional logic on all three:

ORDER BY (IsSponsored = 1) DESC,
         (CASE WHEN IsSponsored = 1 THEN RANK END) ASC,
         PopularityScore DESC;

The first key is an expression on a boolean: this evaluates to true (1) or false (0). I removed the CASE from the third key because it is redundant -- you can put it back if you like.

Upvotes: 1

Related Questions