Date Sorting Odd

[bazzer747 25]

I have a MSSQL table of financial transactions and I retrieve the data with a FDQuery which has the following statement:

'SELECT * FROM tAccounts ORDER BY AccName, TxnDate, Deposit'

And executing that from the TFDQuery gives the correct results - a list in Account order and within that date order. See attached SQLView.jpg, specifically records 501 and 502 (correct date order).

However, when I view this same table in a DBGrid in my application, the order is not the same. See attached DBGView.jpg, note the 20 Feb is before 17th Feb.

 

The field is a Date datatype and there is no alteration to the Select statement anywhere in the application. The DBGrid is the first thing shown in the application and neither Formcreate or FormActivate do anything to alter the data returned.

This 'mis-sorting' shows on several other of the records and my thinking is that in the DBGrid the TxnDate field is sorted in yyyy-mm-dd order, as in '20210217', which numerically comes before '20210220'. So this is what's happening, it seems, but why, when the SQL returns the data in the correct order does the DBGrid 're-sort' in a numeric fashion?

[bazzer747 25]

Ahh, just found something that might explain something!

I have another field called 'ID' - this is a auto-incrementing field to ensure uniqueness of my records. If I show this ID field in the DBGrid then it is clear the order is in 'ID' order! Why should this be, as the Select statement doesn't specify the ID field as something to sort on.

However, I did make this field a Key field when I set the table up. Is this the problem? The DBGrid (for some reason) uses the Keyfield to display data rather than what the Select statement tells it to?

[Stano 143]

DBGrid certainly has the ability to customize the data. Use it. Then discard the ORDER BY section from SQL

[bazzer747 25]

Oops! My bad .... just seen at sometime in the past I set IndexFieldNames on the query to the 'ID' field. Took it off, voila sort is good.

I set that field when I was testing the locate functionality to help speed up finding a record. I can do that in code now switching it on or off when necessary.

Sorry for asking too quickly ....