MySQL - Merging Two Different Sort Orders in Single Query

Other Questions MySQL
1

I’m working on a search feature and need help with a complex sorting requirement. I have ads that need to be sorted by their active date, but there’s a special case. Most ads should be sorted normally by date, but ads with categoryID=7 need to appear at the bottom of the results even if they have newer dates.

Basically I want regular ads (categoryID != 7) sorted by date first, then all categoryID=7 ads sorted by date but always appearing after the regular ones.

Here’s my current query that works for normal sorting but doesn’t handle the special case:

SELECT 
    listings.listingID, 
    listingText, 
    listingActiveDate, 
    categoryID, 
    locations.locationName, 
    listing_photos.photoPath 

FROM listings 

LEFT JOIN locations ON listings.locationID = locations.locationID 
LEFT JOIN listing_photos ON listing_photos.photoDefault = 1 AND listing_photos.listingID = listings.listingID AND listing_photos.photoPath != '' 

WHERE 
        listings.listingType = 0 
    AND listings.listingSold = 0 
    AND listings.listingID IN 
        ( 
             SELECT DISTINCT listings.listingID 
             FROM listings 
             LEFT JOIN listings_featured ON listings_featured.listingID = listings.listingID   
             WHERE listings.listingActive = 1 
        )

ORDER BY categoryID, listings.listingActiveDate DESC

I could run two separate queries and combine them in PHP but the current code structure requires a single query. Is there a way to achieve this custom sorting in one MySQL query?

The desired output should show regular ads first (sorted by date), then categoryID=7 ads at the bottom (also sorted by date):

   ID  | Text         | ActiveDate  |  categoryID | locationName | photoPath
    ___________________________________________________________________________

   1    |  Item Text   |  2010-06-01 |  3          | TX          |  pic.jpg
   4    |  Item Text   |  2010-05-31 |  2          | FL          |  pic1.jpg
   8    |  Item Text   |  2010-03-01 |  4          | TX          |  pic3.jpg
   5    |  Item Text   |  2010-06-01 |  7          | TX          |  pic5.jpg
   7    |  Item Text   |  2010-05-01 |  7          | TX          |  pic7.jpg

Any suggestions would be really helpful!

2

You need a two-level sort with conditional ordering. Your current query sorts by categoryID first, which doesn’t give you the control you want. Here’s the fix - replace your ORDER BY with: ORDER BY (categoryID = 7), listings.listingActiveDate DESC. This creates a custom sort field that puts regular ads before category 7 ads, then sorts by date within each group. MySQL treats boolean expressions as 0 (false) or 1 (true). When categoryID isn’t 7, you get 0. When it is 7, you get 1. Since we’re sorting ascending on this field, all the 0 values (regular ads) come first, then all the 1 values (category 7 ads). The secondary sort by listingActiveDate DESC keeps your date ordering intact within each group. I’ve used this trick tons of times when you need certain records pushed to the bottom regardless of other sorting.

3

Here’s a simple fix - use a compound ORDER BY that treats categoryID as a sorting priority: ORDER BY (categoryID != 7) DESC, listings.listingActiveDate DESC. MySQL’s boolean evaluation does the heavy lifting here. The expression (categoryID != 7) returns 1 for regular ads and 0 for category 7 ads. Sorting DESC on this puts all regular ads (value 1) first, then category 7 ads (value 0). The second sort handles date ordering within each group. I’ve used this same trick in e-commerce platforms where certain product categories needed to rank lower in search results. Keeps everything in one query while giving you that two-tier sorting.

4

try using a CASE in your ORDER BY: ORDER BY CASE WHEN categoryID = 7 THEN 1 ELSE 0 END, listings.listingActiveDate DESC - this will put the regular ads first, then the categoryID=7 ads at the bottom, both sorted by their dates.

5

Add a computed column to your ORDER BY that groups them. Try ORDER BY IF(categoryID = 7, 1, 0), listings.listingActiveDate DESC - the IF function returns 0 for regular ads and 1 for category 7, so normal stuff shows first and category 7 drops to the bottom.