11 Replies Latest reply on May 22, 2012 12:10 PM by rhlilienkamp

    ExecuteSQL - get first row only

    Paul Jansen

      Hi,

       

      I have a calculated query to return the next airings of a broadcast:

       

      Let([

      ~ID = sched::ID_ProgType ;

      ~SQL = "select "StartTS"

      from "Sched"

      where "ID_ProgType" = ?

      and "StartTS" >= current_timestamp

      order by "StartTS" asc" ;

      ~result = ExecuteSQL ( ~SQL; "|"; "¶" ; ~ID )

      ] ;

      ~result

      )

       

       

      This gives me a list of timestamps which could contain 1000 rows and all I want is the first row/value.

       

      I know I can use getvalue( ~result ; 1 ) in filemaker, but I suspect it would be better to have the SQL do it.

       

      Any Ideas?

       

      Thanks

       

      Paul Jansen

        • 1. Re: ExecuteSQL - get first row only
          TomHays

          From my experimentation it doesn't look like FM12's ExecuteSQL supports any of the common methods in SQL for asking for the first row (e.g. Oracle's FIRST(), Microsoft's TOP n, or MySQL's LIMIT n).

           

          In your case there is a workaround using SQL directly.

          Since your data is ordered by the value you want to see in your result, you can skip the (costly) sorting and just ask for the single value you want using MIN().

           

          Let([

          ~ID = sched::ID_ProgType ;

          ~SQL = "select  MIN(\"StartTS\")

          from      \"Sched\"

          where     \"ID_ProgType\" = ?

          and       \"StartTS\" >= current_timestamp;

          ~result = ExecuteSQL ( ~SQL; "|"; "¶" ; ~ID )

          ] ;

          ~result

          )

           

           

          -Tom

          • 2. Re: ExecuteSQL - get first row only
            jrenfrew

            Select min(StartTS) ???

             

            or Select TOP 1 StartTS

             

            and why not

            Let([

            ~ID = sched::ID_ProgType ;

            ~SQL = "select  \"StartTS\"

            from      \"Sched\"

            where     \"ID_ProgType\" = ?

            and       \"StartTS\" >= current_timestamp

            order by \"StartTS\" asc" ] ;

             

            ExecuteSQL ( ~SQL; "|"; "¶" ; ~ID )

            )

            • 3. Re: ExecuteSQL - get first row only
              beverly

              MySQL has this:

               SELECT * FROM `your_table` LIMIT 0, 10 

                This will display the first 10 results from the database. 

               SELECT * FROM `your_table` LIMIT 5, 5 

                This will show records 6, 7, 8, 9, and 10

               

              MS SQL has this:

               

                   SELECT TOP 10 * FROM your_table

               

              FileMaker SQL has this:

               

               

              [nothing similar]

               

              Beverly

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL - get first row only
                Paul Jansen

                Beverly,

                 

                I tried these as well as FIRST and LAST with no joy (althoug I may just have got the syntax wrong as I have almost zero SQL exposure until now.  It seems to me that  SQL statements look more  'simple'  than wot they are

                 

                Thanks

                 

                Paul Jansen

                • 5. Re: ExecuteSQL - get first row only
                  Paul Jansen

                  I like to use a variable to display the result so I can display interim values when developping and debugging calcs.

                   

                  Cheers

                   

                  Paul Jansen

                  • 6. Re: ExecuteSQL - get first row only
                    Paul Jansen

                    Tom,

                     

                    Thanks for this.  Is MIN more efficient than sorting and grabbing the first row?

                     

                    This is great as I can use MAX to get the latest scheduled date for a program as well.  Thanks again

                     

                    Paul Jansen

                    • 7. Re: ExecuteSQL - get first row only
                      beverly

                      Here's a sample of FIRST

                           <http://www.w3schools.com/sql/sql_func_first.asp>

                       

                      I don't believe that FileMaker SQL supports that either, Paul.

                       

                      NO TOP, NO LIMIT, NO FIRST() or LAST()...

                       

                      Beverly

                      • 8. Re: ExecuteSQL - get first row only
                        jrenfrew

                        Is there a definitive white paper yet on which *few* commands are supported? Guess not. Would be a useful thing for someone at HQ to write.

                        As none of the above affect data schema it would be hard to find a reason to leave them out.

                        Unless GetValue(SQL; 1) is SCREAMINGLY faster that SQL LIMIT 1 becaue it is already optimised. Do both of these now evaluat eon the server? This is about not pulling down miles of data over the WAN to then chop down, when what is required is minimum data flow over cables....

                        • 9. Re: ExecuteSQL - get first row only
                          TomHays

                          I expect that one MIN() is a lot more efficient than having the ORDER BY in there.

                           

                          ORDER BY has to sort the records. Even in the most efficient sort algorithm, sorting is less efficient than simply going through the list once and keeping track of the smallest value you saw along the way.

                           

                           

                          -Tom

                          • 10. Re: ExecuteSQL - get first row only
                            beverly

                            j, just the xDBC guide, AFAIK (as far as I know) and any help topics on the script steps and/or function(s) that might be used.

                             

                            These are the facts I use when responding to this forum. I do also test theories sometimes, but try to back them up with the published guides and help topics.

                             

                            Beverly

                            • 11. Re: ExecuteSQL - get first row only
                              rhlilienkamp

                              Since the result of an ExecuteSQL is a text sting you could use the Position function to find the first row delimiter and then use the left function to get the text from the start to that position.