Forums » .NET » .NET »

How will get distinct values from datatable

Posted Date: 27 Feb 2004      Posted By:: Martin Mathew     Member Level: Bronze  Member Rank: 0     Points: 2   Responses: 10


how i will get disctinct values , max and min from datatable

the code is below

dim dt as datatable
Dim drt() As DataRow
drt = dt.Select("distinct Roomtype")

getting error. says that 'System.Data.SyntaxErrorException: Syntax error: Missing operand after 'Roomtype' operator'

datatable contins values
i want the distinct roomtype from datatable
how i will get these values



#49    Author: Mohan      Member Level: Silver      Member Rank: 0     Date: 01/Mar/2004   Rating: 2 out of 52 out of 5     Points: 2

use these two functions to select distinct

private bool ColumnEqual(object A, object B)

if ( A == DBNull.Value && B == DBNull.Value )
return true;
if ( A == DBNull.Value || B == DBNull.Value )
return false;
return ( A.Equals(B) );


public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
DataTable dt = new DataTable(TableName);
dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

object LastValue = null;
foreach (DataRow dr in SourceTable.Select("", FieldName))
if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) )
LastValue = dr[FieldName];
dt.Rows.Add(new object[]{LastValue});
if (ds != null)
return dt;

calling code

// your dataset code

DataTable dt=SelectDistinct("[tbl name]", ds.tables["table name"],"[distinct field name]")

//datatable dt now will contain distinct values


refer to link;EN-US;326176

#79    Author: Cuckoo John Joseph      Member Level: Gold      Member Rank: 0     Date: 05/Mar/2004   Rating: 2 out of 52 out of 5     Points: 2

A Simple Method to Get the Distinct Values.

Use this method.

Parameters :

Datatable from which the distinct values has to be fetched
colName is the name of the column for the distinct values.

so u can call like this.

<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
<B>object[] distinctRoomType = GetDistinctValues(dt,"Roomtype");</B>

Here is the method definition.

<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
public object[] GetDistinctValues(DataTable dtable,string colName)
Hashtable hTable = new Hashtable();
foreach(DataRow drow in dtable.Rows)
object[] objArray = new object[hTable.Keys.Count];
return objArray;


Cheers !

#34222    Author: geekman      Member Level: Bronze      Member Rank: 0     Date: 15/Nov/2005   Rating: 2 out of 52 out of 5     Points: 2

I found this posting very helpful but the function supplied is too slow because it is designed to throw errors so I thought I would add my own varaition of the c# code:

public object[] GetDistinctValues(DataTable dtable, string colName)
Hashtable hTable = new Hashtable();
foreach (DataRow drow in dtable.Rows)
hTable.Add(drow[colName], string.Empty);
object[] objArray = new object[hTable.Keys.Count];
hTable.Keys.CopyTo(objArray, 0);
return objArray;

#146635    Author: suresh      Member Level: Bronze      Member Rank: 0     Date: 24/Aug/2007   Rating: 2 out of 52 out of 5     Points: 2

Hi friends you may find this useful....

public DataRow[] GetDistinctRows(DataTable drows, string colName)
DataRow[] objArray = null;
if (drows != null && colName != null && colName != string.Empty)
Hashtable hTable = new Hashtable();
List<DataRow> distinctRows = new List<DataRow>();

foreach (DataRow drow in drows.Rows)
if (!hTable.ContainsKey(drow[colName].ToString()))
hTable.Add(drow[colName].ToString(), string.Empty);
objArray = new DataRow[distinctRows.Count];
return objArray;

#188572    Author: Ananda Babu Karthikeyan      Member Level: Silver      Member Rank: 4829     Date: 11/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

hello Martin,
Try this code. u'll get distinct table from ur original table(dtTemp).
 dim dt as datatable
Dim dtTemp As DataTable

Calippus Tech (India) Pvt. Ltd.

#190197    Author: chinnu      Member Level: Bronze      Member Rank: 0     Date: 14/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

I think the above answer holds true only for framework 2.0 onwards.

People on earlier frameworks can use the following logic. Loop thru the table using-
for each dr as dataRow in"",columnName)
Keep adding the row to a dummy table(clone of original) when you see a change in the row(columnName) value.

Simple! I think using a hashtable will only slow up things.

#192580    Author: Mr.Rajesh      Member Level: Gold      Member Rank: 91     Date: 21/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

"Select Distinct from Table";
this query will give u distinct table;

Software Engg.

#192660    Author: Ananda Babu Karthikeyan      Member Level: Silver      Member Rank: 4829     Date: 21/Feb/2008   Rating: 2 out of 52 out of 5     Points: 2

hello rajesh ur code will not help. for this

#288526    Author: Sujit Kumar      Member Level: Gold      Member Rank: 219     Date: 28/Aug/2008   Rating: 2 out of 52 out of 5     Points: 4

you can get distinct rows from dataset/datatable
DataSet ds=new DataSet();
//code to fill dataset from database.

DataTable dt=new DataTable();
//here true means you want distict result.

Sujit Kumar

#347483    Author: Gopikrishna      Member Level: Bronze      Member Rank: 0     Date: 16/Feb/2009   Rating: 2 out of 52 out of 5     Points: 3

The above codes need to be slightly modified. They all specify the column name as a string, where as the 'ToTable' method accepts a array of column names.

The code should be,
DataTable dt1=new DataTable();
DataTable dt2=new DataTable();

dt2=dt1.DefaultView.ToTable(true,new string [1]{"columnName"});

Post Reply     

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

  • Return to Discussion Forum
  • Start new thread

  • Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India