How Do I Custom 'Order BY' in MYSQL?

Question

I have the following mysql query:

SELECT merchantNames.strippedName,merchantNames.lastCached,merchantNames.id FROM merchantNames JOIN autoCoupons ON merchantNames.rawName = autoCoupons.merchantName WHERE NOW() > autoCoupons.startDate AND autoCoupons.startDate > merchantNames.lastCached OR NOW() > autoCoupons.endDate AND autoCoupons.endDate > merchantNames.lastCached OR NOW() > autoCoupons.timeAdded AND autoCoupons.timeAdded > merchantNames.lastCached OR merchantNames.lastCached < NOW() - INTERVAL 2 DAY GROUP BY merchantNames.strippedName ORDER BY merchantNames.pageviews DESC

How do I set this query to order the results in such a way that those that meet the first three lines of the WHERE clause criteria are at the top and those that only meet the bottom line are at the bottom?

markz · Accepted Answer · 2012-09-22 17:30:04Z

3

Divide the query in two and use UNION to join the results together.

answered Sep 22, 2012 at 17:30

markz

1,7662 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jmilloy · Accepted Answer · 2012-09-22 18:26:45Z

There are other ways to get the results you want for this query, but for a "custom ORDER BY", you can just order by the result of a boolean clause. DESC will put true (1) results first and false (0) results second. In your case, it's easiest to read and write this query with a nested SELECT (below), but of course you can write a longer query with the explicit conditions in the ORDER BY, which could be significantly faster.

SELECT r.strippedName, r.lastCached, r.id FROM (SELECT merchantNames.strippedName, merchantNames.lastCached, merchantNames.id, merchantNames.pageviews, (NOW() > autoCoupons.startDate AND autoCoupons.startDate > merchantNames.lastCached) AS a, (NOW() > autoCoupons.endDate AND autoCoupons.endDate > merchantNames.lastCached) AS b, (NOW() > autoCoupons.timeAdded AND autoCoupons.timeAdded > merchantNames.lastCached) AS c, (merchantNames.lastCached < NOW() - INTERVAL 2 DAY) AS d FROM merchantNames JOIN autoCoupons ON merchantNames.rawName = autoCoupons.merchantName GROUP BY merchantNames.strippedName) AS r WHERE r.a OR r.b OR r.c OR r.d ORDER BY (r.a OR r.b OR r.c) DESC, pageviews DESC

So if I understand you correctly, the AS at the end of those lines will gather up it's boolean result from both sides of the AND and not just from the tail end of it?
It works, but there are just a few very minor errors in it so, not to be critical, but to help clear it up, I'll explain them. You need to add commas after 'AS a' and 'AS b'. Also, I needed to add 'merchantNames.pageviews,' just below 'merchantNames.id,' in order to be able to use that in the final ORDER BY clause. Thanks again!

Collectives™ on Stack Overflow

How Do I Custom 'Order BY' in MYSQL?

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related