Select Distinct and obtain all row of it

Help
1

Hello guys,

Basically I have a table with more than 10 columns and the first row has Employee Numbers.

I want to do a Select Distinct by Employees numbers to remove duplicates and then I want to keep all values That are from the resulting filtered Employees numbers, meaning I want to keep all row and matching values of the remaining columns.

Can you please help me?

2

HI @mikePontes,

Please refer below link.

Regards,
Arivu

3

Hello arivu,

But its not a filter That I want.

Its simply to remove all duplicates and by That not having 3 or 4 rows with the same Employee Number

4

@mikePontes,

You can try using following query in assign activity,
Datatable dt = Datatable.DefaultView.Totable(true, list of column names)

Regards,
Dominic :slight_smile:

4 Likes
5

I tried a similar solution, but That way only returns the column of Employees Number in this case right? It Will do a Select Distinct in That column, but I Will not have the values of the other ones.

6

@mikePontes, you can give all the column names to achieve it separated by commas or to be precise assign the column names to array or list and use the variable there.

Regards,
Dominic :slight_smile:

7

So if I have:

Column A Column B
1 jose
1 jose
1 jose
2 Mario
3 Paula

IT Will return:

Column A Column B
1 jose
2 Mario
3 Paula

Its like That?

Best Regards,

Miguel Pontes

8

@mikePontes, Exactly.

Regards,
Dominic :slight_smile:

9

I Will try than!

Many thanks mate.

Best Regards

10

@mikePontes, Pleasure to help. Let me know if u have any concern.

Regards,
Dominic :slight_smile:

11

Just clarifying things in a more clear way so it will be easy for Beginner level users :slight_smile:

dataTable.DefaultView.ToTable(true, “Column1”)

or

DataView view = new DataView(table);
DataTable distinct_Values = view.ToTable(true, “Column1”, “Column2” …);

Where:

  • First parameter in ToTable() is a boolean which indicates whether you want distinct rows or not.

  • Second parameter in the ToTable() is the column name based on which we have to select distinct rows.

Only these columns will be in the returned datatable.

Regards…!!
Aksh

8 Likes
12

Why we are using DataView or DefaultView … Can you explain both.

13

Hi Akash,
if i want all distinct rows on the basis of two columns then how can i achieve that.
regards

14

What if this are the case

161029 | Maria
202123 | David Robin
202789 | John the Boy
202789 | Danny Green
202789 | Sample Same
202789 | Sample Same

and the return would be
161029 | Maria
202123 | David Robin
202789 | John the Boy

15

MasterListDatatable.DefaultView.ToTable(True, “Number”, “Name”).AsEnumerable

but the return is
161029 | Maria
202123 | David Robin
202789 | John the Boy
202789 | Danny Green
202789 | Sample Same

17

Basically, a duplicate datarow means having all the values duplicate, so you first need to decide how you want to extract the data, you should have one identifier to consider your data as duplicate else this query won’t work.

18

Perfect!!! it woks has well!!!