7 Replies Latest reply on Mar 20, 2015 10:15 AM by duncanbaker

    ExecuteSQL query to find most recent by date

    duncanbaker

      Hey all

       

      Most likely a simple SQL query but I'm not finding the right way thus far. It's fairly straightforward. I have Customers and they have Jobs. Jobs have dates. Jobs also have a Type as well. From the context of Customers, I want to get the ID of the Job of a particular Type that is the most recent. I could do this via a sorted relationship for each Type but where's the fun in doing something easy...

       

      Here's where I've got to so far:

      ExecuteSQL ( "

      SELECT ( \"__zkpJobID\" )

      FROM JOB_Jobs

      WHERE \"_zkfCustomerID\" = ? AND

      \"Type\" = ?

      ORDER BY \"Date\" DESC

      " ; "" ; "" ;

      __zkpCustomerID ; "Reseal"

      )

       

      Now this gives me a list of all the Job IDs for the Customer of the Type "Reseal" and ordered by date descending. It's pretty close. I could then use GetValue to pull out the first in the list and I think this would get me to where I need to be. But I'm curious how this would be accomplished entirely in eSQL.

       

      Many thanks for any pointers.

        • 2. Re: ExecuteSQL query to find most recent by date
          user19752

          SELECT ( \"__zkpJobID\" )

          FROM JOB_Jobs

          WHERE \"_zkfCustomerID\" = ? AND

          \"Type\" = ?

          AND \"Date\" = (SELECT MAX(\"Date\")

          FROM JOB_Jobs

          WHERE \"_zkfCustomerID\" = ? AND

          \"Type\" = ? )

           

          This may be an answer for "entirely in eSQL", but sometimes using FM function is better on FM...

          And, if there are 2 or more records on same date, the result is different from getting one using ORDER BY.

          • 3. Re: ExecuteSQL query to find most recent by date
            coherentkris

            user19752 is right about the potential issue of using SQL with FETCH in this manner..

             

            If your SQL query contains only ORDER BY \"Date\" DESC and there is more than one record with the same date and that meets the WHERE conditions then the sort order of those records with the same date is unknown.... FETCH will return the first one based on an unknown ordering.

             

            To use FETCH you would have to adjust your ORDER BY to be granular enough so that you get the single resultant record you want in the first position or adjust your where conditions so that you only get the record you want thus eliminating the need for FETCH.

            • 4. Re: ExecuteSQL query to find most recent by date
              duncanbaker

              Thanks both - sorry for the delay in responding. I had thought about a nested eSQL as well as using a Let statement to get the max date to start with but hadn't gone that far with it yet. Took me a couple attempts to get it right but what I ended up with is below. I'll check out FETCH too.

               

              In terms of two records with the same date for the same customer with the same job type - this has a high probability of never happening as the jobs are once a year and close to all day affairs so it's not something to be concerned with in this instance. And this is just for knowing when this type of job last happened to see if it's time for the job to be done again, so again two records producing the same date is no issue.

               

              In terms of performance between the different options:

              GetValue: Pulls all IDs and just grabs the first one

              Nested eSQL: Performs two eSQLs - performance hit?

              FETCH: Although I've not done it, my sense is it's similar to GetValue

               

              Any thoughts on the preferred/best performance method?

               

              Thanks again both - much appreciated.

               

              ExecuteSQL ( "

              SELECT ( \"__zkpJobID\" )

              FROM JOB_Jobs

              WHERE \"_zkfCustomerID\" = ? AND

              \"Type\" = ? AND

              \"Date\" = (

                                 SELECT MAX ( \"Date\" )

                                 FROM JOB_Jobs

                                 WHERE \"_zkfCustomerID\" = ? AND

                                 \"Type\" = ?

              )

              " ; "" ; "" ;

              __zkpCustomerID ; "Reseal" ; __zkpCustomerID ; "Reseal"

              )

              • 5. Re: ExecuteSQL query to find most recent by date
                coherentkris

                Seems to me that the closer something is to impossible to encounter the more damage it can cause when it does. Edge cases are a bitch!

                • 6. Re: ExecuteSQL query to find most recent by date
                  user19752

                  FETCH can be faster than GetValue() since returning less values to caller.

                  Nested SELECT can be faster than 2 ExcecuteSQL() in Let(), but in this case nested use high cost WHERE clause (more than 2 columns) 2 times then ?

                  Max() can be faster than ORDER BY since it need only one value to keep in process.

                   

                  Anyway, performance depends on actual data size and range.

                  • 7. Re: ExecuteSQL query to find most recent by date
                    duncanbaker

                    Thanks for the thoughts and ideas. I'll move forward with one.