using MAX date

[#1]

ok this is what I am trying to do.

in the pic below it is displaying an audit log that and the response from that transaction.  I was hoping to only pull in the latest response per PatID.

How can I do it to only display the most recent submitted date?

Ideally it possible in the load?

I will also have to add other tables at a later point if this is a factor?

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts;
SQL SELECT SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts
FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

[sunny_talwar · Accepted solution]

May be this

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD FirstSortedValue(SubmitGUID, - Submitted) as SubmitGUID,
    Max(Submitted) as Submitted,

    FirstSortedValue(RequestType, - Submitted) as SubmitGUID,

    FirstSortedValue(FacID, - Submitted) as SubmitGUID,

    PatID,

    FirstSortedValue(Request, - Submitted) as SubmitGUID,

    FirstSortedValue(Response, - Submitted) as SubmitGUID,

    FirstSortedValue(ts, - Submitted) as SubmitGUID

Group By PatID;

SQL SELECT SubmitGUID,
    Submitted,
    RequestType,
    FacID,
    PatID,
    Request,
    Response,
    ts
FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

[Anonymous]

So you can do Max(SubmittedDate)

and Group by everything that is not the submitted date after the from.

Or you can do

LastSubmitted:

Load

Submitted date

From  FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';

TableName

First 1

Load

Load

Submitted date

From LastSubmitted

Order By Submitted desc

Left Join

    Submitted,
    RequestType,

    FacID,

    PatID,

    Request,

    Response,

    ts

FROM FWDB.PAT.PatientE1TransactionHistory

where RequestType='MED-D';