Sort by field values from 2 columns - mysql - delphi - dbgrid

Question

I have two columns named priority and state:

• The column priority will only contain field values: urgent and normal.
• The column state will only contain field values: wait, executed and done.

I'm trying to sort these using my dataset CommandText property, so the dbgrid connected to the dataset displays the data in the sort order I've set there.

The sort order should be like this:

1. 1. Rows containing urgent in the priority column should start the DBGrid list.
   2. Then the list should continue with the ones marked as normal in the priority column,
2. 1. followed by the ones marked as wait in the state column,
   2. followed by the ones marked as executed in the state column,
   3. and finally the list ends with the ones marked as done in the state column.

I can't figure out how to write the SQL statement for this, so I can use it in the CommantText property for the dataset.

I am using this currently:

SELECT *
  FROM table_name
  ORDER BY FIELD(column_name, "normal", "urgent") DESC

This works with the first column priority but doesn't take into account the second column state.

Answer 1

You are very close. You just need a second key in the order by:

SELECT *
FROM table_name
ORDER BY FIELD(priority, 'urgent', 'normal'),
         FIELD(state, 'wait', 'executed', 'done')

Notes:

• I removed the DESC from the first key. You are using field(), so put things in the right order.
• This will order the "urgent" by the same three keys. This seems consistent with your question.