InDB: Connect- In DB: Restricted Row Call via MAX

[Preeki]

Hi All,

I've been trying to minimize the data volume which is being pulled into the workflow by "filtering" for the latest date which should be dynamically determined.

I know that this can be achieved via a filter tool but I would ideally not even call the data into the workflow because of the volumes which is creating time-out errors on Alteryx Server.

In SQL the below where clause can achieve this:

Portfoliodate=(SELECT MAX(PortfolioDate) FROM Table_ABC)

I've tried a few clauses in Alteryx but I can't seem to get it right, does anyone know how to do this?

Below is one of the many failed Alteryx attempts:

select PortfolioDate
from {TABLE_abc}
where
PortfolioDate = Max('PortfolioDate')

Looking forward to anyone who can solve this :)

[DataNath]

Hey @Preeki, I've just tested this out on my side and was getting an error until I aliased the MAX date field, so this didn't work:

SELECT MAX(<TableName>.<ColumnName>)
FROM <TableName>

But this did:

SELECT MAX(<TableName>.<ColumnName>) AS <Alias>
FROM <TableName>

[danilang · Accepted solution]

Hi @Preeki 

This syntax works in T-SQL and probably most other versions as well

Select * from TableName
where [Date]=(Select Max([Date]) from TableName)

The [] around Date are because Date is a reserved word in many SQL dialects

Dan