asked on

MySQL ORDER BY IF...

Hi,

I've got the following order by, ORDER BY `date_updated` DESC, `times_downloaded` DESC

I know what I need to do, but not sure how to do it.
I need to do something like ORDER BY IF...
the field date_updated is a timestamp, I need to do order by if ($date_updated !== $date_added AND ($time - $date_updated) <= 604800), `times_downloaded` DESC

So it'll order by times_downloaded descending, but will show updated items at the top of the list regardless of times downloaded if they were updated more recently than 7 days.

I hope I've clearly enough...

Thanks guys.

enggagrawal

use nested query
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

or simple use if statement
http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html

should help

SOLUTION

nasirbest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

i-CONICA

ASKER

Hi, Nasirbest.

Here's the full query, it's not working properly, can you see what's wrong with it? It's returning nothing.

SELECT if((`date_updated` !== `date_added` AND ($time - `date_updated`) <= 604800), 1, 0) AS is_updated, shortname, fullname, category, sicon, date_updated, date_added, beta FROM `soft_data` WHERE category = '{$rowe[$thiscat]}' ORDER BY `is_updated` DESC, `times_downloaded` DESC LIMIT 0, $limit

ASKER CERTIFIED SOLUTION

Sharath S