MYSQL按三列排序

Question

I currently have a MYSQL table set out as: Date Started, last post date, sticky thread

I'm currently doing

ORDER BY sticky DESC, lastpostdate DESC, date DESC

Which almost works, it puts the sticky thread at the top (as it should be), however it is then putting the newest thread at the bottom and threads with replies at the top even if the new thread was created after the reply.

Example table:

 Date         lastpostdate   sticky
 1440291308 | 0            | 0
 1938275440 | 1938293999   | 1
 1440205782 | 1440212200   | 0

It should be ordered 2,1,3 because 2 is a sticky, 1 is a new thread and 3 has a reply but was before 1 was posted.

Answer 1

This is working as expected since you have a lastpostdate set to 0. The subsequent sort columns will ONLY be used if the values are equal on the previous sort column.

For example, if both the sticky values are set to 0, then the order looks to the next sort column. The third column is only consulted if the first and second sort columns are equivalent.

You should have the lastpostdate set to the date when the thread is initially created and this would solve your problem and rid you of the need to have the third sort column.