Custom sort order for dataset after query?

Question

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

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

Answer 1

There is a possibility that the similarities with Jens answer (+1), but the result is slightly different.

:

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' );

( , ), , , :

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

sortvalue temp, ( - ):

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

, :

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

Answer 2

ClientDataset . IndexFieldNames .

, clientdataset .

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

Answer 3

AFAIK ORDER BY.

:

• order_tag .
• .
• order_tag UPDATE #temp_table.
• .

Answer 4

, Internal calc (FieldKind = fkInternalCalc), TDataset. , TClientDataset .

DFM:

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

:

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;