Custom sort order for dataset after executing query?

Question

I want the result set of a database query to have a certain order. The information I want to order by is not contained in the database, but dynamically generated in code (so I cannot use ORDER BY).

Is there a way to sort a dataset after executing the database query? (I don't need indexed access but only want to iterate over all records.)

Answer 1

With a ClientDataset you are able to change the order after executing. Settings IndexFieldNames sorts the dataset.

You can find information here how to connect a clientdataset to another dataset in the same application.

  object DataSetProvider1: TDataSetProvider
    DataSet = MyAdsQuery
    Left = 208
    Top = 88
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 296
    Top = 88
  end

Answer 2

There is a possibility that shares similarities with Jens' answer (+1) but gets to the result in a slightly different fashion.

Given an existing table:

create table somedata (id integer, name char(20));
insert into somedata values ( 1, 'Tim' );
insert into somedata values ( 2, 'Bob' );
insert into somedata values ( 3, 'Joe' );

If you know the desired short order (either by processing the table or some query result from it), create a temp table that has some key value to match the desired rows from the original table and then the sort order data:

create table #sortorder( id integer, sortvalue integer );

Set the sortvalue field in the temp table to contain the desired order (it could be any sortable data type - doesn't have to be integer):

insert into #sortorder values ( 1, 15 );
insert into #sortorder values ( 2, 12 );
insert into #sortorder values ( 3, 5 );

Then generate the results with a join against the table that provides the sort order:

select sd.* from somedata sd, #sortorder so 
         where sd.id = so.id
         order by so.sortvalue; 

Answer 3

AFAIK the only reliable way to sort a dataset is to use ORDER BY.

I would:

1. Add a dummy order_tag field to your query.
2. Dump the results to temporary table.
3. Declare a cursor to iterate over the temporary table and set the order_tag using your custom logic and UPDATE #temp_table statements.
4. Select the data from the temporary table and order by the tag field.

Answer 4

The main trick here would be to use an Internal calc field (FieldKind = fkInternalCalc) if they are supported by your TDataset sub-class. If they aren't, use a TClientDataset as an intermediate.

DFM:

object ClientDataSet1SortField: TIntegerField
  FieldKind = fkInternalCalc
  FieldName = 'SortField'
end

pas:

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.Open('dbuser', 'Hunter2');
  ClientDataSet1.SetProvider(ADOQuery1);  // set ClientDataset provider. This will create a TLocalAppServer provider "in the background"
  ClientDataSet1.Open;
  randomize;
  while not ClientDataSet1.Eof do
  begin
    ClientDataSet1.edit;

    ClientDataSet1SortField.AsInteger := random(100);
    // as ClientDataSet1SortField is fkInternalCalc it doesn't need to be in the query result set, but can be assigned and used for sorting
    ClientDataSet1.Post;
    ClientDataSet1.Next;
  end;
  clientdataset1.IndexFieldNames := 'SortField';
end;