CODEWITHSUNDEEP
phpmysql
NaughtySquid
NaughtySquid

Reputation: 2097

how to sort sql query with fields with a zero last?

I have a sql table that I am sorting by expires time which is a unix timestamp, if there is no expire time I just set that to 0, the problem I am having is when i sort this column in ASC (so expiring first) the rows with a 0 for expiry show up first, is there a way i can get them to show up last?

Here is an example query:

SELECT s.`id`, s.`info`, s.`website`, s.`date`, s.`provider_id`, s.`drmfree`, 
       s.`steam`, s.`desura`, s.savings, s.pwyw, s.pounds, s.dollars, s.euros, 
       s.has_screenshot, s.screenshot_filename, s.`expires`, p.`name` 
FROM `game_sales` s 
      LEFT JOIN `game_sales_provider` p 
            ON s.provider_id = p.provider_id   
WHERE s.`accepted` = 1 AND s.provider_id = ? 
ORDER BY s.expires ASC

Upvotes: 2

Views: 63

Answers (1)

John Woo
John Woo

Reputation: 263813

the simpliest way, boolean arithmetic.

ORDER BY s.expires = 0, s.expires

This works only in mysql, however.

When the value of s.expires is 0, then

 0 = 0 --> returns true or 1

otherwise, if not equal to zero, let's say 1

 1 = 0 --> returns false or 0

if you want to work on other RDBMS as well, use CASE

 ORDER BY CASE WHEN s.expires = 0 THEN 1 ELSE 0 END, s.expires

Upvotes: 7

Related Questions