What is the fastest way to get a max value in a group by table?

[Giansonn]

Hi, I am currently running a data load that looks like this:

[table1]:

Load id,

second_id,

timestamp

from example.qvd

Inner Join

load second_id,

max(timestamp) as timestamp

resident table1

group by second_id;

What this does is it finds only the latest entry by second_id and discards the rest of the entries. However I have tried to load and I know that this is an expensive query. I have seen other options such as `Peek` and `LOAD FieldValue(‘Id’, recno()) as Id AUTOGENERATE FieldValueCount(‘Id’)` and I want to know how to use these in my case.

Any help is much appreciated!

[Saravanan_Desingh]

I think your code looks good. Either INNER or RIGHT join can be used. But the other options like PEEK or AUTOGENERATE will be much more expensive than yours.

[table1]:
Load 	id,
	second_id,
	timestamp
from example.qvd
;

Right Join
Load 	second_id,
	max(timestamp) as timestamp
resident table1
group by second_id;

[sunny_talwar]

The approach provided here https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/ cannot be used with a group by of one or more field... it can only be used to get a max across the qvd. Only thing you can do is to avoid two loads here and do directly find the max from qvd load.

[table1]:
Load second_id,
     max(timestamp) as timestamp
from example.qvd
group by second_id;

[Giansonn]

Hi Sunny, do you think loading from the QVD would be faster than the the INNER and RIGHT joins above? I agree that I have read this in online blog posts like here: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

The resident load is the slowest by far, which is why I was looking for other options.

[sunny_talwar]

I don't think it will be a huge improvement, but worth a shot. One thing which you can consider is Optimize Group By Performance 

[Giansonn]

This is great, so I could order by then group by and I would save time in some cases.

However my query is relatively simple and you can only order tables that are already loaded so I would have to go back to a resident table load.

So now my choice is:

a) resident table with order by then group by

b) QVD load instead of resident load

I will send results as soon as they become available!

[sunny_talwar]

You can always Order your data before you store it into qvd. Then when you load the data from qvd you can directly use Group by. I guess all these are worth a shot, not sure what exactly will work the best.

[Giansonn]

Thats the thing, the data is ordered by the timestamp so maybe going into the QVD is already the best solution

[sunny_talwar]

Order by is needed on second_id

[Giansonn]

Oh yeh my mistake. I will give these data loads a try anyway