How to implement Inner Queries using Aggregates

I've the following table 

enter image description here

and I want to come up with the following table 

enter image description here

I am able to achieve this using the below query:

SELECT * From Table1 T
         JOIN (SELECT clientid,Max(version) As MVer 
               From Table1 Group By clientid) S
         ON T.clientid = S.clientid And T.version  = S.MVer

 This can be done using Advanced SQL Query in OutSystems. But how can I achieve this using Aggregates? 

PS: This question has been taken from Stack Overflow. But I am facing exact similar situation in my own project. I really don't want to use Advanced SQL as it will have a big impact on the screen where the aggregate is used to fetch data it is being used by large number of components.

With just one aggregate you can't and with several, it is a bad practice. Just use the advanced query.

"I really don't want to use Advanced SQL as it will have a big impact on the screen where the aggregate is used to fetch data it is being used by large number of components."

Big impact? I can't really see how.


Hi! 

Try to create a aggregate with the two tables, in the join condition put 

T.clientid = S.clientid


 then group by every field you need , plus the Max(version) and in the filters zone  

T.version  = S.MVer

hope this idea helps

Graça

@Maria and @Marco, please review your answers because you cannot return both the amount associated to the max version AND the max version without an inner query. This is sql 101.

Hi Shanouk,


Like flpmorais already stated, there doesn't need to be an impact at all when using Advanced Queries instead of Aggregates. I can even imagine that trying to accomplish this in an aggregate using grouping can even be slower.

The Outsystems platform optimizes aggregates in a way that it won't get the data from the database for fields you're not using in your action flow and screen. You can easily to this yourself in an advanced query. 

Regards,

flpmorais and Lennart Kraak you both seem to be convincing. However I got a suggestion from another person to use CTE...to take out the inner query as CTE. But I suppose even then I have to use Advanced SQL Query for the CTE and is there any way to join the output of the CTE to the aggregate? I meant to say inner query as CTE, outer as aggregate.

There is no way to join the CTE in an aggregate, so you have to use an advanced query to do that.

What puzzles me still is why you say this will have a big impact on the screen. Can you give an example of why you think this is the case?

Shounak Devbhuti wrote:

flpmorais and Lennart Kraak you both seem to be convincing. However I got a suggestion from another person to use CTE...to take out the inner query as CTE. But I suppose even then I have to use Advanced SQL Query for the CTE and is there any way to join the output of the CTE to the aggregate? I meant to say inner query as CTE, outer as aggregate.

CTE vs subquery is largely a matter of syntax preference (Except if you're trying to implement recursiveness, which you can only do with CTE's). Otherwise the DB engine will usually execute the same with a cte or inner query. Sometimes one can perform better than the other on a particular case, and as such if your query is critical then by all means test and find the best solution, but there isn't a best option that fits ALL cases.


flpmorais wrote:

@Maria and @Marco, please review your answers because you cannot return both the amount associated to the max version AND the max version without an inner query. This is sql 101.

Hi flpmorais,

Thanks for your observation! On this case I'll keep my answer, because there are several options available. And by exploring OutSystems documentation and aggregates functionality, one can return any results needed.

But note, I also think using an advanced SQL is easier for this case. However not the only way to "do this job"! :)

Regards!

Marco Arede wrote:

flpmorais wrote:

@Maria and @Marco, please review your answers because you cannot return both the amount associated to the max version AND the max version without an inner query. This is sql 101.

Hi flpmorais,

Thanks for your observation! On this case I'll keep my answer, because there are several options available. And by exploring OutSystems documentation and aggregates functionality, one can return any results needed.

But note, I also think using an advanced SQL is easier for this case. However not the only way to "do this job"! :)

Regards!


Can you show us how with an OML then? I never figured how to go around that limitation so if there is a way around it I really want to know.

flpmorais wrote:

Can you show us how with an OML then? I never figured how to go around that limitation so if there is a way around it I really want to know.

Better would be to make a workshop and explain some advanced topics w/ aggregates. Would you be able to help and organize a workshop in Lisbon? I could explain this topic there, in a talk, then we could share the knowledge.

You can do this with 2 aggregates:

1) Select clientid,max(version) from TABLE1 AggregateA group by clientid  

then in a loop for each of those records

2) Select * from TABLE1 AggregateB where AggregateB.ClientId=AggregateA.ClientId and AggregateB.Version=AggregateA.Version.

Of course if you want them all in one list, then you have to append them to a masterlist in the loop too.

NOTE: This will be slow due to the (Number of Clients) + 1 database calls, but if you really need to do this with Aggregates only this will work. Its' gross, but it'd work.


Solution

Hi Shounak,

This was already told, but I'll stress it anyway.

Use aggregates whenever possible. But due to its limitations, there are situations where you should use SQL instead. You just have to be careful about selecting only the desired fields, setting max records, etc.

Said that, and only for the sake of intellectual exercise, let me show how to do this specific query with a single aggregate. DON'T use it, as this query needs to do cross joins to work, and this will possibly have huge negative impact on performance.

You need three things: A left Join, Group By and Max and a Group Filter.

1. Left Join your entity with itself, using the ClientId as condition.

2. Add the group by for the attributes of the left entity (Entity1 in my case) and a Max to the Version column of the right entity (in my case, Entity1_2).

3. Add a Group Filter saying that you only want the groups where the Version is equal to the highest version for the client.

The result is what you expect:

But as I stressed, DON'T USE THIS PATTERN. Use the SQL, as it will have a much better performance.

Cheers.

Solution

Eduardo Jauch thanks for this approach. But as per your suggestion...and as per everyone else's suggestions I am using Advanced SQL Query as it will have better performance in this particular scenario. I was just apprehensive to convert my aggregate to Advanced SQL as my real aggregate is having a lot of joins and a lot of filters and Dynamic sorts associated to it. Also there were quite a number of widgets which was dependent on it.

Anyways now i have successfully converted it to Advanced SQL and it is running perfectly alright.

Thanks all for all the help and ideas. :)