AnsweredAssumed Answered

ExecuteSQL query to find most recent by date

Question asked by duncanbaker on Mar 19, 2015
Latest reply on Mar 20, 2015 by 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.

Outcomes