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

Answer 2

In your SELECT, use a CASE statement that takes the value of date modified if not null otherwise date posted. Then use that new "calculated" columnn for your ORDER BY.

Answer 3

It seems like I'm giving this advice three times a week here on SO, maybe I should just give up and let it go :-) Nah, I don't think so:

Don't use per-row functions in your column calculations (or order by clauses) if you want your database to scale well. You should check performance for your particular case (measure, don't guess) but doing calculations when reading the database will generally affect your ability to scale (this won't matter for your address book database but the shops I work in have huge amounts of data).

The number of "how do I get my DB to go faster?" questions far outweighs the number of "how do I use less space?" ones. It's a well-trodden path to sacrifice disk space for performance.

The right time to do calculations is when the data changes. That way the cost of the changes is amortised across all reads.

My advice is to create another column such as dtLastAction to contain the ordering value then use an insert/update trigger to set it to the same as dtModified if not null, or dtPosted if dtModified is null. Technically, this violates 3NF but that's okay if you know what you're doing, and the triggers guarantee data consistency in that case.

Then index on the dtLastAction column and see the speed of your queries improve at the (lesser) cost of some extra work during inserts and updates. I say lesser because the vast majority of database tables are read more often than written (obviously this method is useless if your particular situation is one of the very rare exceptions).

Alternatively, for this particular case, you could set dtModified and dtPosted to the same value when creating the entry, meaning that dtModified will never be null. You can still detect posts that have never been modified by the fact that these two datetime values are identical.