Max Subquery

[#1]

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LastRunId:

load max(run_id) resident Aggregates;

AggregatesForLastRun:

load * Resident Aggregates

where run_id >= LastRunId;

Any way to make this work?

[ronaldocarrijo]

Thus have the same effect, more simply:

Aggregates:

LOAD Max(RUN_ID)           as RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

GROUP BY EXECUTION_DATE;

[Anonymous]

I assume there can't be run_id > LastRunId, can be only run_id = LastRunId
Try this:

Aggregates:
LOAD
RUN_ID,
EXECUTION_DATE
FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LastRunId:
inner keep (Aggregates)
load max(RUN_ID)  as RUN_ID
resident Aggregates;

DROP TABLE LastRunId

[#4]

Your solution doesn't work....

[#5]

I want a solution like Michael that uses the table already in memory instead of reloading again from the source and wasting time.

[#6]

My last code can't find column [AggregatesForLastRun.MAX_RUN_ID]...

Raw:

LOAD RUN_ID,

     EXECUTION_DATE,

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

qualify *;

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

AggregatesForLastRun:

load max([Aggregates.RUN_ID]) as MAX_RUN_ID Resident Aggregates

Group BY [Aggregates.RUN_ID];

LastRunId:

load * Resident Aggregates

where [Aggregates.RUN_ID] >= [AggregatesForLastRun.MAX_RUN_ID];

unqualify *;

[#7]

In SQL this is just so easy...Why do they have to complicate?

select *

from Aggregates A

where A.RUN_ID = select max(run_id) from Aggregates A2 group by A2.RUN_ID

[ronaldocarrijo]

Works yes my friend, attached an example I did.

[#9]

This also doesn't work:

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\FOO.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

inner keep (Aggregates)

load max(RUN_ID)  as MAX_RUN_ID

resident Aggregates;

LastRunId:

load * Resident Aggregates

where [RUN_ID] = MAX_RUN_ID;

[#10]

But you're loading the same table twice.

If you have 1 million rows that;s not feasible in terms of performance.