I need to set a default order column and then another order column can be supplied as a parameter to accommodate user preferences. However, there is a possibility that the column I set as default may also be chosen by the user. In such a case, will it be successfully applied to match the user’s selection? This is the example:
SELECT id, name, code, age, create_time FROM data_table
order by create_time asc, create_time desc
In this case, the query result would be sorted by 'create_time' column ascent or descent?
I wonder the actual result in this case, otherwise the output would be wrong. Actually I tested it in Navicat, and it's sorted ascent as expected, but I don't know why.
order by takes arbitrary expressions, not just columns, and the order of rows is determined by the leftmost order expression with a difference. There is no requirement that a column only be used in one expression. In your case they would be sorted ascending; the descending expression will only be evaluated when create_time of two rows being compared is equal, in which case it will make no difference.
If create_time is the same for two rows, which order they will be returned in is not defined and may even vary from one query to the next.
I recommend always having sufficient order by columns to unambiguously determine order; in this case add additional order by expressions to distinguish between any two rows (for instance, id if that is a unique identifier).