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.)

Richmal

What type of dataset are you using? – Eighteenth

The column you want to use to order is calculated? (using OnCalcFields) – Proselytize

@Proselytize Currently there is no column, just an algorithm that can calculate a "weight" per record based on other data in the application. This weight is the sort criterion. – Richmal

Answer 1

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; 

Wear

I like this the most for large datasets, because the amount of data to transfer seems the least. For small datasets I would probably go for one of the CDS solutions. – Richmal

Answer 2

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

Bowlds

Good idea, but this would copy all my data to the CDS. So it is an option for small data sets. – Richmal

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.

Kickoff

That is on the server side, you can of course reorder on the client side as well, like some of the other answers suggest. – Lacewing

+1 I like the idea because I don't want to transfer all data at once to a CDS. Mark's answer goes one step further by not copying all data to a temp table. – Richmal

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;

Blossom