CODEWITHSUNDEEP
mysql
BudwiseЯ
BudwiseЯ

Reputation: 1826

How to use two values to define order in MySQL but priorize one of them?

I have a discussion board with the following table structure:

+-------------------------------+
|       TABLE: MESSAGES         |
+-------------------------------+
| id             | int(11)      |
+-------------------------------+
| parent         | int(11)      |
+-------------------------------+
| author         | int(11)      |
+-------------------------------+
| last_reply     | datetime     |
+-------------------------------+
| written        | datetime     |
+-------------------------------+
| title          | varchar(256) |
+-------------------------------+
| content        | text         |
+-------------------------------+

The default value for last_reply is NULL. If a thread is replied, the last_reply of all messages in it will be set to the datetime of the last reply.

What I'm trying to achieve is an order of the conversations where both last_reply and written will be considered, but where the last_reply will be priorized over written when available.

So if last_reply is available, we use that value for ordering. If not, we use written.

I apologize if this is badly explained.

My best shot so far has been this:

SELECT *, COALESCE(last_reply,written) AS tmp FROM messages
WHERE parent = 0 ORDER BY written DESC, tmp DESC

This doesn't work correctly.

If you get my point, please guide me to the correct direction :)

Thanks.

Upvotes: 0

Views: 64

Answers (2)

Ander2
Ander2

Reputation: 5658

Try this:

SELECT *, COALESCE(last_reply,0) AS with_replay FROM messages
WHERE parent = 0
ORDER BY with_replay DESC,written DESC

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65304

SELECT * FROM messages
WHERE PARENT=0
ORDER BY IFNULL(last_reply,written) DESC

Upvotes: 3

Related Questions