0

I want to sort a MySQL table using ALTER TABLE (so it stays sorted), and it works fine when I simply put in 1 column name like this:

ALTER TABLE data ORDER BY upvotes DESC;

But I want to sort it based upon the difference of two values, and when I do that I get "Unrecognized alter operation."

This is the code I want to work:

ALTER TABLE data ORDER BY (upvotes-downvotes) DESC;

Is there a way to do this, or am I just getting the syntax wrong? Thanks in advance!

Julian George
  • 101
  • 1
  • 2
  • 9
  • 2
    I don't think this is possible. You could add another column named score (which would be upvotes-downvotes) and sort by that. – juergen d Aug 12 '17 at 18:53
  • InnoDB tables are always stored in primary key order. Using ALTER TABLE...ORDER BY works only for MyISAM tables, and [you probably shouldn't be using MyISAM.](https://stackoverflow.com/questions/20148/myisam-versus-innodb/17706717#17706717) – Bill Karwin Aug 12 '17 at 19:08

1 Answers1

1

As mysql documentation on alter table says (highlighting is mine):

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.

As @juergend suggested in his comment, the possible workaround is to store the results of the calculation in a field and use that for sorting.

Also, I'm not sure why you are still stuck with myisam table type. I would definitely consider switching over to innodb.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • thanks for the reply, I'm trying to stay away from making more columns, so is there a way I could calculate the points and store it into a variable using code in the query instead of making it into a new column? – Julian George Aug 12 '17 at 21:43
  • @JulianGeorge Pls see the highlighted sentence again that I quoted from the mysql manual. – Shadow Aug 12 '17 at 21:44