order by [field] desc limit N equivalent?

[ioannagr]

Hi all,

I want to load for each person the max("id:) corresponding to their lastupdate .

TempTable:
LOAD
max("ID") as "max id"

resident Table1
group by "PersonID"
order by lastupdate desc
limit 1;

That last line is not acceptable.  Any ideas of how i should fix this? I don't want the table to contain just one 1 line, but 1 line for every personID.

Thanks in advance

[Levi_Turner]

It's not immediately clear to me what you're looking to get with ORDER BY. But I believe this is ultimately what you're looking for:

1. Single row per key
2. Maximum value for this key

If so then this is an example ported from an app of mine:

tmp:
LOAD * INLINE [
Hostname,Message2
Node1,100
Node1,10
Node2,20
Node2,200
];

NodeRAM:
inner keep 
load
    Hostname,
    round(max((Message2))) as AvailableRAM
resident tmp
group by Hostname;

Drop Table [tmp];

Hope that helps

[ioannagr]

@Levi_Turner  hi and thanks for your input.

What you suggest is that I make a load inline table to arrange the value I want to have available for every key?

If so, I'm afraid I can't cause there are millions of rows.

The way my table is without order by and limit, returns for some ids two rows because of different last updates in the source table.

So i just want to keep the one row with the key with the most recent last update. 😕 

[Levi_Turner]

No, I do not suggest doing an INLINE LOAD for your use case. I was providing an INLINE LOAD example so you can see the example yourself. You would be loading however you get Table1. Likewise, I am personally getting the table named tmp through other means.

[ioannagr]

@Levi_Turner  i think i understood. From what i am aware of, keep makes changes to the source table as well, and maybe that's why you drop it later on, but i also need the source table as it is. 😕