[Solved] Sort DBGrid column by integer values - not alpha

[bobonwhidbey]

This WIKI article helped a lot, trying to sort a DBGrid column with alpha values.
http://wiki.freepascal.org/Grids_Reference_Page#Sorting_columns_or_rows_in_DBGrid_with_sort_arrows_in_column_header

How can I sort a column correctly if it contains all integers. For my SQLite db I would normally use a Cast(fieldname as integer) in a query but I don't know how to do that within a HeaderClick event.

[jesusr]

Integer, floats, alpha here it works. It seems you don't have to do anything special.

[bobonwhidbey]

You're right. I had to learn how to typecast the SQL statements. For example, this works great:

Code: Pascal  [Select][+][-]

1.   srt := 'Select * FROM MyFile CAST(' + Column.FieldName + ' AS float)' ;
2.   TSQLQuery(DataSource.DataSet).SQL.Text := srt;
3.   TSQLQuery(DataSource.DataSet).Open;

With a StringGrid I can use a DoCompareCells function for specialized sorting.Is there any way to do something similar with a DBGrid?

[jesusr]

No, a TStringGrid has all the data always available and it owns it, a DbGrid does not own the data, it merely show it, the data comes from the dataset. So it's not possible to do this.

[Ericktux]

you tried with event "OntitleClick"
regards 

[bobonwhidbey]

yes

[rvk]

TDBGrid gets its sorting from the connected TDataset and that one gets it from the connected database through the correct query and index-setting. You can't change it with a custom-client-side procedure.

If you have a limited number of records you could read all records in a memory-dataset and sort it from there.

Otherwise you need to find a way to sort it correctly by the database-server. There are a number of options depending on database-server used (for example "COLLATE NOCASE ASC" in SQLite).

What kind of custom sorting are you trying to do?

[bobonwhidbey]

The difficult sort is on a column of bridge bids; e.g.
3NT
1S
2CX
3C
3S

While the suits will sort correctly (clubs, diamonds, hearts, spades) "no trump" or "N" should sort after spades. Of course it doesn't alphabetically. The sloppy solution I've come up with is to have the database hold "T" rather than an "N" for no trump bids - and then make sure that the DrawCell displays "T" correctly as an "N".

[rvk]

The difficult sort is on a column of bridge bids; e.g.
3NT
1S
2CX
3C
3S

While the suits will sort correctly (clubs, diamonds, hearts, spades) "no trump" or "N" should sort after spades. Of course it doesn't alphabetically. The sloppy solution I've come up with is to have the database hold "T" rather than an "N" for no trump bids - and then make sure that the DrawCell displays "T" correctly as an "N".

(I take it the order you want is 1S, 2CX, 3C, 3S, 3NT)

Your solution is a valid one. But there is still a possibility to sort NT before S, H, D, C. Or C, D, H, S and NT if you prefer, low to high.

Code: SQL  [Select][+][-]

1. SELECT *
2. FROM BIDDINGS
3. ORDER BY
4.   SUBSTR(BID,1,1),
5.   CASE SUBSTR(BID,2,1)
6.     WHEN 'C' THEN 0
7.     WHEN 'D' THEN 1
8.     WHEN 'H' THEN 2
9.     WHEN 'S' THEN 3
10.     WHEN 'N' THEN 4
11.     ELSE 5
12.   END

I haven't tested this but it should sort by number of tricks (first character in BID) and inside that in order of C, D, H, S and NT.

[bobonwhidbey]

WOW. That's really clever RVK. Yes - it works perfectly. Thank you. In case others would like to see the complete code, here's the important stuff

Code: Pascal  [Select][+][-]

1. procedure TScoreBoard.GridTitleClick(Column: TColumn);
2. var
3.   srt, dir: string;
4. begin
5.   if Column.Index = 7 then
6.     with Grid do begin
7.       if FmtOpts.SortCol = Column.Index then
8.         FmtOpts.SortUp := not FmtOpts.SortUp
9.       else
10.         FmtOpts.SortUp := True;
11.       FmtOpts.SortCol := Column.Index;
12.       if FmtOpts.Sortup then
13.         dir := ''
14.       else
15.         dir := 'DESC';
16.  
17.       srt := 'SELECT * FROM ScoreBd ORDER BY SUBSTR(Result,1,1) '+dir+',' +
18.         ' CASE SUBSTR(Result,2,1) WHEN ''C'' THEN 1 ' +
19.         'WHEN ''D'' THEN 2 WHEN ''H'' THEN 3  WHEN ''S'' THEN 4 ' +
20.         '  WHEN ''N'' THEN 5 ELSE 0 END '+dir;
21.  
22.       TSQLQuery(DataSource.DataSet).Close;
23.       TSQLQuery(DataSource.DataSet).SQL.Text := srt;
24.       TSQLQuery(DataSource.DataSet).Open;
25.     end
26.   else begin  
27.  
28. etc.
29.