I'm trying to get a list of dates to bung in a drop down and having a bit of a problem with the ordering

SELECT
LIST(DISTINCT ORDERED CAST(_DatePaid AS VARCHAR(10)),#13),
LIST(DISTINCT ORDERED CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'DD/MM/YYYY'),#13)
FROM Transactions

It looks like the ORDERED is being carried out after the CAST and DATE FORMAT is executed

I can easily just use the result set and loop through it to build my picklist but I was hoping to just assign the field. Anyone have a way of getting things in date order with LIST. I'm trying a subselect but can't get it to work.

Roy Lambert

-- Untested!

SubSELECT??

SELECT
 LIST(CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'DD/MM/YYYY'), #13)
FROM
( SELECT DISTINCT
   _DatePaid
 FROM Transactions
 ORDER BY DatePaid) as T

Adam


Good idea but the result you get is

17/10/2018
05/06/2019
29/10/2018

I have no idea how that is sorted

Roy Lambert

>>17/10/2018
>>05/06/2019
>>29/10/2018

>>I have no idea how that is sorted

Weird! I tested it on my own data & I swear it was sorted! (might be that the original data was naturally date-sorted). I feel it should work ... the order of the Sub-select, ought to determine the ordering of the main select, but it seems the LIST statement is following some other logic when it generates the list.

Another way to go would be a lot more cumbersome, but might work (please don't laugh!):

- In the LIST statement FORMAT YYYY/MM/DD and order the list in reverse order.

LIST(ORDERED DESC CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'YYYY/MM/DD'), #13)

That gets the data into the list in the correct order, but not formatted as you would want, and with the "first" item last.

Then write a function REVERSEORDER to reverse the WHOLE STRING.

It would have to look for separators (either '/' or #13) and not separate the sections inbetween these (the DD MM YYYY) ... it wouldn't be super-easy to write, but it would be doable.

REVERSEORDER(LIST(ORDERED DESC CAST(_DatePaid AS VARCHAR(10) DATE FORMAT 'YYYY/MM/DD'), #13))

Sorry if this is just madness ... it would be easier if Tim got the LIST to preserve the ordering of the SUBSELECT.

Adam



>Weird! I tested it on my own data & I swear it was sorted! (might be that the original data was naturally date-sorted). I feel it should work ... the order of the Sub-select, ought to determine the ordering of the main select, but it seems the LIST statement is following some other logic when it generates the list.

Data in the right order would be my guess as well

>Another way to go would be a lot more cumbersome, but might work (please don't laugh!):

It probable would but I'll go with just using the result set. It just seemed that using LIST would be more elegant  - as my wife often says - Woops

>it would be easier if Tim got the LIST to preserve the ordering of the SUBSELECT.

Yup - I'm sure there will be a good reason why not - either that or its one of those things that never even occurs to you.

Thanks for trying

Roy