How to remove column in DataSet and distinct rows to find only group names?

Question

I have DataSet which has 3 columns.

Name    -    insurance comp. name     -     treatmentDate
Ali Boz      SGK                            12.04.09
Ali Boz      SGK                            14.04.09
Ali Boz      SGK                            16.04.09
Ali Boz      SGK                            18.04.09
Veli Aş      AKBANK                         10.04.09
Veli Aş      AKBANK                         11.04.09
Veli Aş      AKBANK                         12.04.09
Veli Aş      AKBANK                         13.04.09
Cuneyt Sel   ING BANK                       01.05.09
Cuneyt Sel   ING BANK                       02.05.09
Cuneyt Sel   ING BANK                       05.05.09
Cuneyt Sel   ING BANK                       19.05.09

I want to firstly find only insurance comp. names

SGK
AKBANK
ING BANK

Then i want to sort by name and date

But all those things in a DataSet (cause i want to retrive all rows from db).

Do you have any advice to me?

Answer 1

Not sure what exactly your asking, but to retrieve insurance company names, you could simply execute the following:

SELECT DISTINCT [insurance comp. name]
FROM [tablename]

To sort all records as you mentioned:

SELECT *
FROM [tablename]
ORDER BY [insurance comp. name], [name], [treatmentdate]

Answer 2

If you can use LINQ, this will give you the insurance company names:

var Names = (from Row in YourDataSet.YourTable
             select Row.InsuranceCompanyName).Distinct();

You could also add .ToArray() or .ToList() or orderby depending on your needs, if necessary.

If you can't use LINQ or change the SQL call, it's more complicated.

Answer 3

It's pretty simple using DataView.ToTable :

DataView view = new DataView(table);
DataTable distinctTable = view.ToTable(true, "insurance comp. name");