Take the 2-minute tour ×

I have a query I need to perform to show search results for a project. What needs to happen, I need to sort the results by the "horsesActiveDate" and this applies to all of them except for any ad with the adtypesID=7. Those results are sorted by date but they must always result after all other ads.

So I will have all my ads in the result set be ordered by the Active Date AND adtypesID != 7. After that, I need all adtypesID=7 to be sorted by Active Date and appended at the bottom of all the results.

I'm hoping to put this in one query instead of two and appending them together in PHP. The way the code is written, I have to find a way to get it all in one query.

So here is my original query which has worked great until I had to ad the adtypesID=7 which has different sorting requirements.

This is the query that exists now that doesn't take into account the adtypesID for sorting.


FROM horses 

LEFT JOIN states ON horses.statesID = states.statesID 
LEFT JOIN horses_images ON horses_images.himagesDefault = 1 AND horses_images.horsesID = horses.horsesID AND horses_images.himagesPath != '' 

        horses.horsesStud = 0 
    AND horses.horsesSold = 0 
    AND horses.horsesID IN 
             SELECT DISTINCT horses.horsesID 
             FROM horses 
             LEFT JOIN horses_featured ON horses_featured.horsesID = horses.horsesID   
             WHERE horses.horsesActive = 1 

ORDER BY adtypesID, horses.horsesActiveDate DESC

My first thought was to do two queries where one looked for all the ads that did not contain adtypesID=7 and sort those as the query does, then run a second query to find only those ads with adtypesID=7 and sort those by date. Then take those two results and append them to each other. Since I need to get this all into one query, I can't use a php function to do that.

Is there a way to merge the two query results one after the other in mysql? Is there a better way to run this query that will accomplish this sorting?

The Ideal Results would be as below (I modified the column names so they would be shorter):

   ID  | Description  | ActiveDate  |  adtypesID  | statesName  | himagesPath

   3    |  Ad Text     |  06-01-2010 |  3          | OK          |  image.jpg
   2    |  Ad Text     |  05-31-2010 |  2          | LA          |  image1.jpg
   9    |  Ad Text     |  03-01-2010 |  4          | OK          |  image3.jpg
   6    |  Ad Text     |  06-01-2010 |  7          | OK          |  image5.jpg
   6    |  Ad Text     |  05-01-2010 |  7          | OK          |  image5.jpg
   6    |  Ad Text     |  04-01-2010 |  7          | OK          |  image5.jpg

Any help that can be provided will be greatly appreciated!

share|improve this question

4 Answers 4

I am not sure about the exact syntax in MySQL, but something like

ORDER BY case when adtypesID = 7 then 2 else 1 end ASC, horses.horsesActiveDate DESC

would work in many other SQL dielects. Note that most SQL dialects allow the order by to not only be a column, but an expression.

share|improve this answer
Thank you to you both for helping me with this. I had tried something like this but kept making errors. This worked like a charm! Appreciate your help a lot. –  Chad Jun 30 '10 at 17:53

This should work:

ORDER BY (adtypesID = 7) ASC, horses.horsesActiveDate DESC
share|improve this answer

Use a Union to append two queries together, like this:

SELECT whatever FROM wherever ORDER BY something AND adtypesID!=7


SELECT another FROM somewhere ORDER BY whocares AND adtypesID=7


share|improve this answer
I am not sure if this works in MySQL, but it definitely is not standard SQL. In standard SQL, you can only have one order by for a union, not separate ones for each select. –  Frank Jun 30 '10 at 18:03

I re-wrote your query as:

   SELECT h.horsesID, 
LEFT JOIN STATES s ON s.stateid = h.statesID
LEFT JOIN HORSES_IMAGES hi ON hi.horsesID = h.horsesID 
                          AND hi.himagesDefault = 1 
                          AND hi.himagesPath != ''
LEFT JOIN HORSES_FEATURED hf ON hf.horsesID = h.horsesID   
    WHERE h.horsesStud = 0 
      AND h.horsesSold = 0
      AND h.horsesActive = 1
 ORDER BY (adtypesID = 7) ASC, h.horsesActiveDate DESC

The IN subquery, using a LEFT JOIN and such, will mean that any horse record whose horsesActive value is 1 will be returned - regardless if they have an associated HORSES_FEATURED record. I leave it to you for checking your data to decide if it should really be an INNER JOIN. Likewise for the STATES table relationship...

share|improve this answer

Your Answer


