But why are we creating duplicates?
Because my join creates them?
Up! Please don't make me do another list to sort distinct again.
Yes, distinct would be helpful
Hey , alternate way to distinct a Aggregate get to append all to list record and distinct that list record using DISTINCTRECORDLIST in SORTRECORDLIST component.
@Rocky yes, but that takes extra time, especially for long lists, and also forces the entire query output to be retrieved, which can degrade performance.
As for a workaround, you can Group on all the output columns you need, which will implicitely make them distinct. Also not a very nice solution, but better than post-processing.
just do it!
Hopefully, the aggregate accomodates all the Advanced Query's capabilities as easy as possible.
regards,
bb
Isn't this how the "Group by" function already works in aggregates?
Hi everyone!
I think that if we have chance to apply DISTINCT (like select distinct in SQL) we have more power in aggregates. Many times I use advanced SQL just because of the "Distinct".
Best regards,
Ricardo
Hi everyone!Documentation refers a way to get distinct values inside Aggregates, by applying "Group by" to all the intended columns: https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/Get_Distinct_Values_from_the_DatabaseIsn't this covering the majority of the scenarios you are facing?Cheers
Hi Carlos!
Yes, it does, but it is very cumbersome if you have a large number of Attributes (I don't think the Aggregate editor allows grouping on multiple Attributes at once?). Also, it's not that intuitive. Having a dedicated "distinct" would be a valuable addition I think.
In terms of the database, I'm not sure whether a DISTINCT is a lighter procedure than GROUPing, but intuitively I'd say it is, so there's also a performance issue to consider.
Generally Group By is faster than Distinct. Regardless I’d that, if you have that many columns to group (maybe 10-15 or more), then you are doing it wrong. That’s one of the main villains in regards to slow queries and unresponsive systems.
Group By is simply the *wrong* thing to do, conceptually. If someone looks at an Aggregate that has almost every column as a "group by" but no aggregate function like a MAX or SUM or whatever, are they going to think, "wow, I bet this was to make it distinct!" or are they going to think, "someone messed up"?
J.Ja
Justin,
Agreed. I often see people doing this kind of stuff in order to avoid making multiple queries (that is, in a single aggregate, make joins that repeats some rows just for a column that should be filtered and then grouping every column of the main table again).
That’s the wrong approach for this kind of stuff. Sub-queries are much more feasible, but not possible in aggregates which are much easier to create.
Yes please! Select distinct will be hugely helpful.
If I have a table called Teams, one to many with a table of Members, which is one to many with a table of Activities, and I want :
Team Name Count of Members Count of ActivitiesTeam A 12 144Team B 5 25
Then I don't see a way to do that with an Aggregate.
If we had COUNT DISTINCT, then it would be a simple join and Group By Team Name.
COUNT DISTINCT would be different from a "normal" DISTINCT though, I think this is a different Idea.
For a specific list with n attributes, have an input to execute the "group by" in the list with the list of columns received as input.
I see this as a new tab like "Sorting" where you could select the column you want to remove duplicates.
DBs don't really have a provision for this; DISTINCT is an all-or-nothing approach. That said, if you are asking for a DISTINCT option on Aggregates, I will merge this with the other Ideas out there for that.
As Justin already explained this idea already exists, what justing promised but didn't do is the merge of your idea into the original idea. I will do that now.