Record for Max()

[#1]

Hi All,

I want to fetch only those records from the DB where the RUN_ID=maximum.

e.g-I have five fields like ID,SF,NATION,STATE,RUN_ID where RUN_ID=1,2,3.. etc.

I want to pull the records for ID,SF,NATION,STATE if(RUN_ID=3)Everytime i refresh,i don't want to update this RUN_ID.

Can i use Max(Run_ID) or any suggestions?

[tresesco]

Though I did not understand exactly what you meant by:

Everytime i refresh,i don't want to update this RUN_ID.

In a generic way, you can try like:

Max:

Load

          Max(RUN_ID) as MaxID

from <>;

Let vMax=Peek('MaxID');

Load

     *

From <> Where RUN_ID=$(vMax);

Drop Table Max;

[#3]

you can use a sub-query:

LOAD *;

SQL

select ID,SF,NATION,STATE,RUN_ID

from table

where RUN_ID = (select MAX(RUN_ID) from table);

Thanks,

Angad

[alexandros17]

Hi,

in the script is easier than in objects:

Suppose that your table (myTable) has field ID, SF,NATION,STATE as primary Key:

TempTab:

Load ID,SF,NATION,STATE Max(RUN_ID) as RUN_ID Resident myTable group by ID,SF,NATION,STATE;

Then do:

Load * resident myTable;

left join

Load *, '1' as flagMax resident TempTab;

Drop table temptab;

Now you can use in object simply the flag "flagMax" to write needed rows

Hope it helps