6
 Followers
96
 Likes

Aggregate with distinct

Aggregates & Queries
On our radar
Aggregate should have an option to remove duplicates.

I see this as a new tab like "Sorting" where you could select the column you want to remove duplicates.
Created on 5 Oct 2015
Comments (38)
Well, Simple Queries are the way of the past. From version 9, they are no longer available.
Well, I think I'm the only one that miss the Simple Queries... but we should let it go anyway...
Edit: I was talking about aggregate and not simple query.
Although we can work around this issue (by grouping all the fields) I still think there could be this way to simplify queries
@Andre: I miss them too!

@Ricardo, agreed, like a DISTINCT
Edit: I was talking about aggregate and not simple query.
Although we can work around this issue (by grouping all the fields) I still think there could be this way to simplify queries
This idea is great.
I'm hoping to see this implemented as soon as possible.
Agree! the distinct option would improve the aggregates alot.
How could this not to be implemented yet?
@Ricardo, good idea!
This was suggested 5 years ago for Simple Queries, marked as "Done" but never implemented as far as I know:

http://www.outsystems.com/ideas/260/distinct-select-possible-on-a-simple-query

Then again (with the same result) four years ago:

http://www.outsystems.com/ideas/846/distinct-parameter-in-simple-queries

This really is needed.

J.Ja
yes!


up
up
Please foresee this for aggregates

up

DO IT!!

up

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.

PLEASE DO IT!!!

just do it!

Hopefully, the aggregate accomodates all the Advanced Query's capabilities as easy as possible.

regards,

bb

Certainly!


Please do it.

up

Up \o/

Isn't this how the "Group by" function already works in aggregates?

Yes please!

Merged this idea with 'DISTINCT in aggregates' (created on 01 Oct 2018 14:17:03 by Ricardo Pereira)

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



This comment was:
- originally posted on idea 'DISTINCT in aggregates' (created on 01 Oct 2018 by Ricardo Pereira)
- merged to idea 'Aggregate with distinct' on 02 Oct 2018 04:14:37 by Justin James

Changed the category to Aggregates & Queries




This comment was:
- originally posted on idea 'DISTINCT in aggregates' (created on 01 Oct 2018 by Ricardo Pereira)
- merged to idea 'Aggregate with distinct' on 02 Oct 2018 04:14:37 by Justin James

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_Database
Isn'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.

views
2411
Followers
6