Sorting on two or more columns

Years ago, I developed a system whereby the data in a grid could be sorted according to any column, ascending or descending, by means of clicking on the column's title: shades of Excel, or indeed Priority. Now and then I wondered how difficult it would be to sort according to two or more columns, where any of them could be ascending or descending. This couldn't or shouldn't be something that is preprogrammed as one person might want to sort according to column 2 ascending, then column 1 descending, then by column 3 ascending then by column 4 descending - there are too many options to do this in advance. Another person might want a different order.

I thought about this seriously during this morning's early dog walk. I already have several bits that would need to be put together, primarily the dual list box, in order to choose which fields are required, and the advanced addindex code that allows sorting to be according to several fields where some could be descending. Yet there are a few more bits that need to be added to the dual list box: there must be a way of marking in the list box whether a field should be sorted descending, and the order of the fields in the list box is critical (not alphabetical!). There were a few more gotchas that became clear only after I started.

Of course, each 'sorter' would have to be saved according to screen and user - this put me in mind of the SavedQueries table. After a quick examination, I realised that I needed to add only one field to this table in order to be able to support the 'sorters' - a simple binary field. By means of a default parameter, I didn't even have to change any of the existing calls that insert data into this table. The details table required the addition of the two fields that I mentioned previously: the sort order and whether the field should be sorted ascending or descending.

As opposed to the SavedQueries code, here I was able to create a stand-alone unit that receives as input the name of a query (a clientdataset, to be accurate) and a form's identity number. The interface took a while to figure out: a user should be able to choose sorters that she has previously defined, create a new sorter, update the configuration of the sorter and delete the sorter. Assuming that one is creating a new sorter, first one defines the sorter itself (by means of the existing code for creating a new saved query), then the window opens up in the dual list box configuration with all the query's fields all on one side. Chosen fields can be transferred to the left hand side and optionally marked as descending. The list of fields can then be saved (that's the OK button on the panel) or ignored; the sorter can then be transferred to the calling form (the bottom OK field) or cancelled. At the moment, there's no way of changing the order of the fields that have already been selected; one has to remove the appropriate fields then add them again in the required order.

The four fields chosen on the left (one descending) then have to be transferred to the calling form and an index built. At first, I thought that the code necessary to build the special index would have to be in the calling form, but after a while I realised that I could do this in the sorter form as the form receives a parameter to the query thus making this unit even more self contained. One small problem that I had was that the index would have to use each field's fieldname and not the displaylabel (these are displayed in the dialog). Once I got past this, I had to build the index - I wanted to use a constant index name, but as this would be invariant, I would have to delete the index should it exist then recreate it, meaning that I would have to check whether the index existed before trying to delete it. Counting from 'fieldslist.clear', the next three lines check whether the special index exists then delete it; the fourth line add the new index, the fifth line causes the query to be sorted according to the new index and the sixth line causes the query to be displayed properly.

 if (showmodal = mrOK) and (cbSorters.text <> '') then
  begin
   s:= '';
   reverse:= '';
   dm.qSorterDistList.Params[0].asinteger:= getCBValue (cbSorters);
   dm.qSorterDistList.Open;
   while not dm.qSorterDistList.eof do
    begin
     n:= dm.qSorterDistList.fields[0].asinteger;
     s:= s + query.fields[n].fieldname + ';';
     if dm.qSorterDistList.fields[1].asinteger = 1
      then reverse:= reverse + query.fields[n].fieldname + ';';
     dm.qSorterDistList.next
    end;
   dm.qSorterDistList.close;
   setlength (s, length (s) - 1);
   if reverse <> '' then setlength (reverse, length (reverse) - 1);

   with query do
    begin
     fieldslist.clear;
     getindexnames (fieldslist);
     if fieldslist.indexof (SpecialIDXName) <> -1
      then deleteindex (SpecialIDXName);
     addindex (SpecialIDXName, s, [], reverse, '', 0);
     indexname:= SpecialIDXName;
     first;
    end;
  end;
 fieldslist.free;
end;

I don't know how valuable the above code is because some of the hard work is done behind the scenes. I also don't know in the end how useful this unit will be - it only makes sense where there are both repeating and non-repeating data in a grid. For example, the main 'dockets' form of the management program displays docket number and customer name, amongst other things. Each docket number appears only once, so sorting by docket number is natural. But sorting by customer can display the docket numbers for each customer in a random matter; defining an index 'customer;docket id' would sort this out (pun intended). 

Looking at this code later, one clear optimisation can be seen: there's no reason to rebuild the strings 's' and 'reverse' every time. This can be done only when the sorter is defined, saving the values in the database table, so that they can be retrieved whenever the sorter is chosen.

Edit from 23/10/23: I saw how to get rid of the need to check whether the special index exists. Instead of using a constant name for the index, it is now built on GetTickCount and so is effectively random.

   with query do
    begin
     idx:= 'idx' + inttostr (gettickcount mod 16384);
     addindex (idx, s, [], reverse, '', 0);
     indexname:= idx;
     first;
    end;     

---

This day in history:

Blog #	Date	Title	Tags
209	20/10/2009	Lucky break	TV series, Films, Olivia Williams, William and Mary, Cold feet
419	20/10/2011	Firebird DB management tool (4) - Corrections	Programming, SQL, dbExpress
641	20/10/2013	More health issues	Health, CPAP
767	20/10/2014	The seach for serendipity	DBA, SQL
894	20/10/2015	Vinyl log 23	Vinyl log, 1971
1268	20/10/2019	This must be the place (2)	Personal
1350	20/10/2020	DBA update	DBA