MySQL Help for MySQL!

Question

I want to place an order in a specific order. I know that I can change the whole database, but then I will need to change the whole code base.

What I have is a column in the “games” table called “status”.

So...

SELECT * FROM games ORDER BY status ASC -- Will retrieve results going from 0 then 1 then 2 

I am looking to be able to order it at 1, then 0, then 2.

Any ideas ???

Answer 1

If I understood correctly, using the CASE expression:

  SELECT g.* FROM GAMES g ORDER BY CASE g.status WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 3 END 

Using the FIND_IN_SET function :

  SELECT g.* FROM GAMES g ORDER BY FIND_IN_SET(g.status, '0,1,2') 

Using the FIELD function :

  SELECT g.* FROM GAMES g ORDER BY FIELD(g.status, 0, 1, 2) 

Answer 2

You can use FIELD ():

 SELECT * FROM games ORDER BY FIELD(status, 1, 0, 2) 

But it might be better to explain a little more of what you want to do, that is, what is in your table.

Answer 3

I was going to post the same method as OMG Ponies, so +1 is there. Here is a useful site where I learned this technique:

http://www.shawnolson.net/a/722/mysql-arbitrary-ordering.html

Answer 4

Another option is to have a validation table for the status column called VAL_GAME_STATUS. GAMES.STATUS will be the foreign key for VAL_GAME_STATUS, and VAL_GAME_STATUS migt looks like

 CREATE TABLE VAL_GAME_STATUS -- Oracle format - modify as needed for other DB (STATUS NUMBER PRIMARY KEY, DESCRIPTION VARCHAR2(50) NOT NULL UNIQUE, SORT_ORDER NUMBER NOT NULL UNIQUE); 

Given this, a request to get sorted results from GAMES becomes

 SELECT g.* FROM GAMES g INNER JOIN VAL_GAME_STATUS s USING (STATUS) ORDER BY s.SORT_ORDER; 

The downside here is that you need to make a connection to sort the data, but this is a minor hit, since VAL_GAME_STATUS is a small table that can probably be stored in memory. The advantage is that you can change the sort order on the fly without touching your code. It is also easy to add new status values ​​with the appropriate sort order.

Share and enjoy.