FQL SORT BY Date, Time

[Joshua]

Hi,

I'm trying to sort my FQL by date and then time

ExecuteSQL("SELECT DISTINCT OrderID FROM Tests ORDER BY ODate, OTime DESC"; ""; ""; "") 

but for some reason it is not working. It gives me the list of OrderID's but not the Descending order that I asked for. Instead, it gives it to me as if I had placed ... ORDER BY ODate ASC. It sorts correctly if I have ODate DESC by itself but... I also need to have it sort it by time.

Other information:

It's a Specify Calculation

Filemaker Pro Advance 13

Windows 8

 

Thanks in advance

[eos]

ExecuteSQL("SELECT DISTINCT OrderID FROM Tests ORDER BY ODate, OTime DESC"; ""; ""; "") 

​Try

ExecuteSQL ( "
  SELECT DISTINCT OrderID
  FROM Tests
  ORDER BY ODate DESC, OTime DESC
  " ; "" ; ""
) 

PS: There is one "" too many, i.e. an unused “parameter”, in your function; doesn't do any harm, but is confusing.

Edited May 7, 2015 by eos

[Joshua]

eos,

Thank you for your reply. I already tried that and the results are the same as if I wrote it as ODate, OTime DESC. It seems to be a bug. Do you have any other suggestion for a work around?

[eos]

Works for me:

ExecuteSQL ( "
  SELECT theDate, theTime
  FROM People
  ORDER BY theDate DESC, theTime DESC
  " ; Char(9) & Char(9) ; ""
)

returns

2015-05-07        22:37:32
2015-05-07        22:37:27
2015-05-07        22:37:25
2015-05-07        22:37:20
2015-05-07        22:37:14
2015-05-07        22:37:08
2015-05-06        22:37:36
2015-05-06        22:37:34
2015-05-06        22:37:30
2015-05-06        22:37:22
2015-05-06        22:37:18

Do you need DISTINCT?