Appian version of below SQL query

Question

How can I write below SQL query through query entity in Appian 
 Select * from A where A.colmn1 = max(A.colmn1)

Chris · Accepted Answer

There are 2 primary options here: 
 Option 1 (most scalable), create a view in the database which contains the query that matches your main CDT, but points to the view instead. Then utilize a simple a!queryEntity() over the view CDT to always return the max row. Best for overhead and scalability. I also always recommend for views, changing the "SELECT *" into a list of all your columns specifically such as "SELECT id, userName, amount, date.." etc, as changes to the table structure will not automatically be reflected in your view and this can cause confusion for debugging without being able to see which columns are listed (the view would have to be re-published as-is, no changes, utilizing "*" if you change the underlying table structure later). 
 Option 2 requires querying all values into Appian to utilize the max() function, then a second query to the data source to filter for the max value you are looking for. If you are concerned with scalability (as we normally are) and your data set growing, this method will limit at 1 MB of data during the initial query, and slow down as it approaches that number. For cases where, say, users enter 1 to 100 rows per request, and you want to find the max row of a specific request, this should be fine. Essentially if the submissions are unlimited, you will want to steer to option 1 (I like Option 1 for all of these cases anyway). 
 Option 2 Sample: 
 a!localVariables(
 local!yourDataPoint: "id", /* change to your data point */
 local!max: tointeger(
 max( 
 property(
 a!queryEntity(
 entity: cons!COE_DS_SAMPLE, /* change to your data store */
 fetchTotalCount: false,
 query: a!query(
 paginginfo: a!pagingInfo(1,-1),
 selection: a!querySelection(
 columns: {
 a!queryColumn(
 field: local!yourDataPoint
 )
 }
 )
 )
 ).data,
 local!yourDataPoint,
 0
 )
 )
 ),
 local!maxRow: a!queryEntity(
 entity: cons!COE_DS_SAMPLE, /* change to your data store */
 fetchTotalCount: false,
 query: a!query(
 paginginfo: a!pagingInfo(1,1),
 filter: a!queryFilter(
 field: local!yourDataPoint,
 operator: "=",
 value: local!max
 )
 )
 ).data,
 
 local!maxRow
)

Mike Schmitt · Answer

TBH, this seems like a lot of running around / bending-over-backwards to do essentially this: a!localVariables(
 local!maxRow: a!queryEntity(
 entity: cons!COE_DS_SAMPLE,
 query: a!query(
 paginginfo: a!pagingInfo(
 startIndex: 1,
 batchSize: 1,
 sort: a!sortInfo( /* this should get the max row by "colmn1" in almost all cases */
 field: "colmn1",
 ascending: false()
 )
 )
 )
 ).data,

 local!maxRow
)

Mike Schmitt · Answer

Chris said: create a view in the database which contains the query that matches your main CDT, but points to the view instead. 
 BTW, if we're bringing bespoke Views into this, I hope you're aware (as I only learned several weeks ago now) that with the current DB version(s), we can now use Window Functions, including row_number() which is powerful . Basically you can arbitrarily define partitions within a given query result and assign row numbers to the resulting rows, and then sort (and even select by) those row numbers. This means that in your new view, you could create a row_number that applies against all rows in the table, sorting inverted by the target date column, then just query the result for "row 1".

Appian version of below SQL query

Top Replies