SQL: how do i order by a field if its not null else use another field

Question

i want to sort my Posts by dtModified (datetime modified) if its not null (post modified b4), else sort by dtPosted (datetime posted)

Answer 1

You can use COALESCE:

ORDER BY COALESCE(dtModified, dtPosted)

Another option is to use the MySQL specific function IFNULL instead of COALESCE.

---

Testing on MySQL:

CREATE TABLE table1 (dtModified DATETIME NULL, dtPosted DATETIME NOT NULL);
INSERT INTO table1 (dtModified, dtPosted) VALUES
('2010-07-31 10:00:00', '2010-07-30 10:00:00'),
(NULL                 , '2010-07-31 09:00:00'),
('2010-07-31 08:00:00', '2010-07-30 10:00:00');

SELECT dtModified, dtPosted
FROM table1
ORDER BY COALESCE(dtModified, dtPosted)

Results:

dtModified           dtPosted
2010-07-31 08:00:00  2010-07-30 10:00:00
NULL                 2010-07-31 09:00:00
2010-07-31 10:00:00  2010-07-30 10:00:00