2 Replies Latest reply on Jun 5, 2014 9:57 AM by deathrobot

    SQL sorting on number and date fields

    deathrobot

      Title

      SQL sorting on number and date fields

      Post

           This works for text fields:

           ExecuteSQL (
           "SELECT
           id
            
           FROM
           Table
            
           ORDER BY LOWER ( Field ) DESC
           ";
            
           ""; ""; "" )
            
           but not for number and date fields. Is there some SQL magic that can account for those types of fields, or do I need to create a calculation field that stores these values as text?
            
           Thanks!
           Michael

        • 1. Re: SQL sorting on number and date fields
          philmodjunk

               Well Lower is a text function so it makes no sense to use it for sorting on a number or date field. Text based sorting IS case sensitive in SQL so using either Upper or Lower makes sense for text based sorting.

               Or are you getting unexpected results when you sort a number field and you are not using the Lower function?

               If so, make sure that you are really using a field of type number and not of type text to store your numbers. At least that's the most common reason numbers in FileMaker might not sort as expected.

          • 2. Re: SQL sorting on number and date fields
            deathrobot

                 Thanks, Phil. Dumb error on my part. You're right; removing LOWER fixes it.

                 Thanks,
                 MIchael