Syntax error near "ORDER BY order DESC" in MySQL

Question

Why, when I try to do an order by query, do I always get an error telling me to check the syntax by the ORDER BY 'order' DESC?

Here's my query:

SELECT * FROM posts ORDER BY order DESC;

What am I doing wrong?

Answer 1

order is a reserved word in SQL; case does not matter. It must be quoted when used as an identifier. From the MySQL Reserved Words documentation:

Certain words such as SELECT, DELETE, or BIGINT [or ORDER] are reserved and require special treatment for use as identifiers such as table and column names.

Traditional MySQL quotes:

SELECT * FROM posts ORDER BY `order` DESC;

Proper (ANSI) SQL quotes (some databases support [order] as well):

SELECT * FROM posts ORDER BY "order" DESC;

Although I would consider renaming the column to avoid such confusing issues in the future.

Answer 2

Order is a reserved keyword.

Try,

SELECT * FROM posts ORDER BY `order` DESC;

Answer 3

The column name is order which is a keyword. You need to do this:

SELECT * FROM posts ORDER BY `order` DESC;