locked
select distinct from datatable RRS feed

  • Question

  • I have .net DataTable.

    Need to select data which would select first column distinct and other relevant column.

    If on sql table then i would fire "select distinct employeename, address, city, code from employeetable where lastmodified-date > '2014-12-12'"

    now i have source data in .net datatable and need to filter in the above way.

    DataView.ToTable(distinct=true, column names)  does not work as distinct is applied for all the columns.

    Thanks,

    Subodh

    Tuesday, April 21, 2015 12:31 PM

Answers

  • I have .net DataTable.

    Need to select data which would select first column distinct and other relevant column.

    If on sql table then i would fire "select distinct employeename, address, city, code from employeetable where lastmodified-date > '2014-12-12'"

    now i have source data in .net datatable and need to filter in the above way.

    DataView.ToTable(distinct=true, column names)  does not work as distinct is applied for all the columns.

    Subodh,

    In SQL Server, the "distinct" in your SQL query is applied to all columns specified in the SELECT as well. The "distinct" in both the T-SQL syntax and the DataView.ToTable(....) work the same way. The example that Magnus has provided also does a "distinct" on all the columns specified in the SELECT.

    What does your data look like and how would you like to see it look?



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Wednesday, April 22, 2015 1:50 AM edited for clarity
    • Marked as answer by Subodh Patil Thursday, April 23, 2015 9:08 AM
    Wednesday, April 22, 2015 1:48 AM

All replies

  • You could use LINQ to extract the rows and then create a new DataTable. Here is an example for you:

                //the original table:
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("employeename"));
                table.Columns.Add(new DataColumn("address"));
                table.Columns.Add(new DataColumn("city"));
                table.Columns.Add(new DataColumn("code"));
                table.Columns.Add(new DataColumn("something"));
                table.Columns.Add(new DataColumn("lastmodified-date",typeof(DateTime)));
                //sample data:
                table.Rows.Add("a", "address", "city", "c1", "x", DateTime.Parse("2015-01-01"));
                table.Rows.Add("a", "address", "city", "c1", "x", DateTime.Parse("2015-01-02"));
                table.Rows.Add("b", "address", "city", "c1", "x", DateTime.Parse("2015-01-01"));
                table.Rows.Add("c", "address", "city", "c1", "x", DateTime.Parse("2015-01-01"));
                table.Rows.Add("d", "address", "city", "c1", "x", DateTime.Parse("2015-01-01"));
                table.Rows.Add("a", "address", "city", "c1", "x1", DateTime.Parse("2015-01-01"));
    
     
    
                //select distinct rows:
                var rows = table.AsEnumerable()
                    .Where(row => Convert.ToDateTime(row["lastmodified-date"]) > DateTime.Parse("2014-12-12"))
                    .Select(row => new
                    {
                        employeename = row.Field<string>("employeename"),
                        address = row.Field<string>("address"),
                        city = row.Field<string>("city"),
                        code = row.Field<string>("code")
                    }).Distinct();
    
    
                //create a new DataTable:
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add(new DataColumn("employeename"));
                dtNew.Columns.Add(new DataColumn("address"));
                dtNew.Columns.Add(new DataColumn("city"));
                dtNew.Columns.Add(new DataColumn("code"));
                foreach (var row in rows)
                    dtNew.Rows.Add(row.employeename, row.address, row.city, row.code);
    
                //dtNew contains the distinct data
    


    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't post several questions in the same thread.

    Tuesday, April 21, 2015 3:14 PM
  • I have .net DataTable.

    Need to select data which would select first column distinct and other relevant column.

    If on sql table then i would fire "select distinct employeename, address, city, code from employeetable where lastmodified-date > '2014-12-12'"

    now i have source data in .net datatable and need to filter in the above way.

    DataView.ToTable(distinct=true, column names)  does not work as distinct is applied for all the columns.

    Subodh,

    In SQL Server, the "distinct" in your SQL query is applied to all columns specified in the SELECT as well. The "distinct" in both the T-SQL syntax and the DataView.ToTable(....) work the same way. The example that Magnus has provided also does a "distinct" on all the columns specified in the SELECT.

    What does your data look like and how would you like to see it look?



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Wednesday, April 22, 2015 1:50 AM edited for clarity
    • Marked as answer by Subodh Patil Thursday, April 23, 2015 9:08 AM
    Wednesday, April 22, 2015 1:48 AM
  • You are right Bonnie.

    Thanks.

    Thursday, April 23, 2015 9:08 AM
  • You're welcome, Subodh! Glad I could help.  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, April 23, 2015 1:49 PM