CODEWITHSUNDEEP
c#linqdatatabledataset
Yesudass Moses
Yesudass Moses

Reputation: 1859

How to Take Top 10 Distinct Rows order by Date

I have a Datatable having records like this... 

JID                      Date                     RecentChatString
----------------------------------------------------------------------- 
[email protected]         11/Nov/2013 11:53:00        Hi 
[email protected]         11/Nov/2013 11:53:10        Hello 
[email protected]         11/Nov/2013 11:54:00        Good Morning 
[email protected]         11/Nov/2013 12:03:00        Ok 
[email protected]         11/Nov/2013 12:05:10        Please reply 
[email protected]         11/Nov/2013 12:15:00        Good after noon 
[email protected]         11/Nov/2013 12:15:50        Ok bye

I want to get Top 10 Distinct Records sorted by Date and, Means only the recent chat of each JID..

JID                      Date                     RecentChatString
-----------------------------------------------------------------------
[email protected]         11/Nov/2013 12:05:10        Please reply
[email protected]         11/Nov/2013 12:15:50        Ok bye

Now I have a code like this. I can get the top 10 records sorted by date using this code. But, It contains duplicate JID's. Please help me. (recent_index is a DataTable)

DataRow recent_dr = recent_index.NewRow();
recent_dr["JID"] = RosterId;
recent_dr["Date"] = DateTime.Now;
recent_dr["RecentChatString"] = _chatline;
recent_index.Rows.Add(recent_dr);

DataTable dtt = new DataTable("RecentChats");
dtt.Columns.Add("JID", Type.GetType("System.String"));
dtt.Columns.Add("Date", Type.GetType("System.DateTime"));
dtt.Columns.Add("RecentChatString", Type.GetType("System.String"));

IEnumerable<DataRow> recentTen = recent_index.AsEnumerable().OrderByDescending(x=>x["Date"]).Take(10);
recentTen.CopyToDataTable(dtt, LoadOption.OverwriteChanges);

dtt.WriteXml(s + "\\FPhoneData\\chats\\index.xml");

Upvotes: 1

Views: 957

Answers (4)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236278

Tim's solution is correct. Here is just alternative solution with declarative query:

var latestMessages =
    from r in recent_index.AsEnumerable()
    group r by r.Field<string>("JID") into g
    let latest = g.OrderByDescending(r => r.Field<DateTime>("Date")).First()
    orderby latest.Field<DateTime>("Date")
    select latest;

var recent_dr = latestMessages.Take(10).CopyToDataTable();

Upvotes: 0

liquidsnake786
liquidsnake786

Reputation: 447

Use .Distinct on your collection use a custom class to do distinct checking

private class DistinctChecker: IEqualityComparer<YourRowType>
{
}

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

DataTable recentTen = recent_index.AsEnumerable()
    .OrderByDescending(r => r.Field<DateTime>("Date"))
    .GroupBy(r => r.Field<string>("JID"))
    .Take(10)
    .Select(g => g.First())
    .CopyToDataTable();

Upvotes: 5

SoftSan
SoftSan

Reputation: 2472

Try following:

DataView view = new DataView(recent_index);
DataTable distinctValues = view.ToTable(true, "JID", "Date" ...);

Upvotes: 0

Related Questions