DBGrid / DataSet fails to sort as per sql statement set in CommandText

Question

I'm using the following in my CommandText property of the DataSet I'm using:

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

It should sort the data I'm displaying in the DBGrid connected to this DataSet, 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.

But It doesn't, well actually it kind of does, but it's actually backwards. Here is a quick video I've made to show you whats happening, maybe you can get a clearer view this way:

Video of what's happening

I'm guessing it's because of either the ID column I'm using or the Date column but if so, I have no idea how and why.

This is how those 2 columns look like/are set up:

• ID column is set as Primary and Unique and Auto_Increment - that's it, no Index or any of the other options If it's not those 2 columns the problem, then maybe the DBGrid?

I'm using RAD Studio 10 Seattle, dbExpress components (TSimpleDataSet, etc) and MySQL db

Any thoughts on how to fix this? thanks!

Answer 1

You are making life unnecessarily difficult for yourself going about it the way you are.

It's not necessary to get the server to do the sorting (by using an ORDER BY clause and it's arguably better to do the sorting in the client rather than on the server, because the client typically has computing power to spare whereas the server may not.

So, this is my suggested way of going about it:

1. Drop the ORDER BY from your SQL and just do a a SELECT * [...].

2. Replace your SimpleDataSet by a ClientDataSet and define persistent TFields on it. The reason for making this change is so as to be able to create two persistent fields of type fkInternalCalc.

3. In the TFields editor in the Object Inspector, define two fkInternalCalc fields called something like PriorityCode and StateCode.

4. Set the IndexFieldNames property of your dataset to 'PriorityCode;StateCode'.

5. In the OnCalcFields event of your dataset, calculate values for the PriorityCode and StateCode that will give the sort order you wish the data rows to have.

Something like:

procedure TForm1.ClientDataSet1CalcFields(DataSet: TDataSet);
var
  S : String;
  PriorityCodeField,
  StateCodeField : TField;
  iValue : Integer;
begin
  PriorityCodeField := ClientDataset1.FieldByName('PriorityCode');
  StateCodeField := ClientDataset1.FieldByName('StateCode');
  S := ClientDataset1.FieldByName('Priority').AsString;
  if S = 'urgent' then
    iValue := 1
  else
    if S = 'normal' then
      iValue := 2
    else
      iValue := 999;
  PriorityCodeField.AsInteger := iValue;

  S := ClientDataset1.FieldByName('State').AsString;
  if S = 'wait' then
    iValue := 1
  else
    if S = 'executed' then
      iValue := 2
    else
      if S = 'done' then
        iValue := 3
      else
        iValue := 999;
  StateCodeField.AsInteger := iValue;

end;

Actually, it would be better (faster, less overhead) if you avoid using FieldByName and just use the fields that the Fields that the OI's Tfields editor creates, since these will be automatically bound to the ClientDataSet's data fields when it is opened.

Btw, it's useful to bear in mind that although a TClientDataSet cannot be sorted on a field defined in the TFields editor as Calculated, it can be sorted on an InternalCalc field.