Delphi: Sort ClientDataSet by datetime asc, nulls last

Question

I need to sort a ClientDataSet by a DateTime field, e.g. next_due_date, in ascending order and with null values last.

I will be adding new records at runtime and I am not allowed to execute the SQL query again.

Can you use an index on a ClientDataSet in such a way?

You did not specify your database, some SQL's allow you to specify `NULL FIRST/LAST`. See: https://stackoverflow.com/a/12767777/650492 — Johan, Jun 18 '18 at 16:33
As usual MySQL uses an undocumented `-` syntax: https://stackoverflow.com/a/8174026/650492 — Johan, Jun 18 '18 at 16:35
You can create a calculated field, or for example set to your `next_due_date` field `DefaultExpression` to `DateTimeToStr(MinDateTime)`. Not elegant, but could work (but MIDAS is not elegant too and well, works in some way). I'm saying this because I don't think you can define index in such way. — Victoria, Jun 18 '18 at 17:40
@Victoria Thanks, but I think I will use a sql query to get the data initially sorted and simply insert the new records at the right position by moving my cursor as explained here: [https://stackoverflow.com/a/9993382/9658630](https://stackoverflow.com/a/9993382/9658630) — Michael Skrzypietz, Jun 19 '18 at 07:50

score 0 · Accepted Answer · answered Jun 19 '18 at 12:14

0

you could create internal calculated field in cds, populate it as your needs dictate and create an index referring to that field

answered Jun 19 '18 at 12:14

Vladimir Ulchenko

1 Answers1