Without knowing the specifics, it seems like you should be able to keep this all within one query. However, you can always use Query Builder to do a Max on the table in question with the dates, and then extract that value to substitute in another piece of SQL Script. An example of the script from Query Builder working on the movie database, Rentals table where the date column has the date of rental, the script emitted would be like:
New SQL Query(
Connection( "ODBC:DSN=SQLServer SQBTest;UID=sa;PWD=%_PWD_%;APP=JMP;WSID=machine;DATABASE=SQBTest;" ),
QueryName( "g6_Rentals" ),
Select(
Column(
"OrderDate",
"t1",
Alias( "Maximum-OrderDate" ),
SavedJMPName( "OrderDate" ),
Aggregation( "Maximum" ),
JMP Name( "Maximum-OrderDate", 0 ),
Numeric Format( "m/d/y", "-1", "NO", "" )
)
),
From( Table( "g6_Rentals", Schema( "SQBTest" ), Alias( "t1" ) ) )
) << Run Foreground();
val = Column( 1 )[1];
Show( As Date( val ) );
Here was are just doing an Aggregate of Maximum on the date column. The resulting table has one row and column, and we take the value out of that and show it to the log to make sure it is correct.
Brian Corcoran
JMP Development
... View more
