Select multiple items from DataTable based on multiple criteria...

Joseph

Hello, I have a bit of an interesting scenario that I need assistance with. I have a DataTable that has five columns. The last column is the date that the record was created. The first column is for the product name. I need to be able to pull the oldest date per product. Is this possible without Linq? I know that I can sort the DataTable and get the oldest overall. And, I could also just set up DataTables per product and pull the information from there. Is there a way to accomplish this with one DataTable? Snippet below:

            dt.Columns.Add("CaseNumber");

            dt.Columns.Add("Subject");

            dt.Columns.Add("Description");

            dt.Columns.Add("Owner");

            dt.Columns.Add("CreatedDate");



                for (int i = 0; i < qr.records.Length; i++)

                {

                    Case newCase = (Case)qr.records[i];

                    DataRow dr = dt.NewRow();

                    dr["CaseNumber"] = newCase.CaseNumber;

                    dr["Subject"] = newCase.Subject;

                    dr["Description"] = newCase.Description;

                    dr["Owner"] = newCase.Owner.Name1;

                    dr["CreatedDate"] = newCase.CreatedDate;

                    dt.Rows.Add(dr);

So, what I need is to find all records by a particular product in the "Owner" column and then select the oldest case by the "CreatedDate" column.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs17y ago
I understand about LINQ. It is not easy to understand. It is also powerful, so it is worth learning. We need to get a good book to help us. I want to learn LINQ much more; I am sure it is worth knowing. I think the documentation of LINQ is quite inadequate.
Sam Hobbs17y ago
Good to hear you have it figured out. This forum's software does not notify us when someone replies to a thread so I did not see your reply before now. The AM/PM problem and formatting are easily solved so it is good that you figured it out.
Joseph17y ago
Nevermind, I got her situated. Thanks, again.
Joseph17y ago
Hello Sam,

The query that you built for me in LINQ works beautifully sans one problem. It is pulling the oldest date up to the time but it does not take in to account AM/PM. So, the earliest records is in this format "10/29/2009 10:00:45 PM", however, the earliest records should be "10/29/2009 10:00:53 AM". Know of any solution? It would be much easier if it were in 24-hour format instead of 12, but, unfortunatley, it isn't :). And, I had to change "query.Max(p => p.CreatedDate))" to "query.Min..." in order for it to pull the older times. I really appreciate all of your help thus far.
Joseph17y ago
I'll give your solutions a try in a bit. Pretty covered up right now but I will let you know what works. I will try it with both LINQ and SortedList to see which works best. The only reason I didn't want to do LINQ is because I'm very unfamiliar with it :). And, you can just use HTML tags to format your code when you post on here. The site doesn't respect simplet line breaks; you have to put a "
" at the end of the line for it to start a new line. Again, I will update you in just a while to let you know how things pan out. Thanks so much for the suggestions btw.
Sam Hobbs17y ago
And if you can tell me how to post code so it is easier to use, then I will be happy to fix the code I posted so you can use it. The software for this forum is not written as well as software that I develop. In other words, this forum's software is not good.
Sam Hobbs17y ago
I am sorry, I did not see the part where you say not using LINQ. If the DataTable is sorted by the case number or whatever it is you want the earliest date for, then simply iterate through the rows and save the row with the earliest date. For each row, if the case number in the new row is the same as the case number saved previously, then compare the dates and if the new row has an ealier date, then save the new row as the saved row. If the case number in the new row is not the same as the case number saved previously, then first process the previous row; you have the row for the earliest date for the case number. Then simply save the new row as the saved row. See my answer in http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=69512 for a sample when the data is sorted. If the data is not sorted, then you can iterate through the rows and create a SortedList as you go and save a row for each case number. The following example will show how to do that. SortedList sl = new SortedList(); foreach (DataRow r in Cases.Rows) { if (!sl.ContainsKey(r.Field("CaseNumber"))) sl.Add(r.Field("CaseNumber"), r); else { if (r.Field("CreatedDate") < sl[r.Field("CaseNumber")].Field("CreatedDate")) sl[r.Field("CaseNumber")] = r; } } foreach (DataRow r in sl.Values) Console.WriteLine(r.Field("CaseNumber") + " " + r.Field("CreatedDate"));
jinge17y ago
I've given the answer in your another post. http://www.c-sharpcorner.com/Forums/ShowMessages.aspx?ThreadID=69512
Sam Hobbs17y ago
You can use LINQ to help with that. I am sorry I don't know LINQ very well but I think the following is a working sample. DataTable Cases = new DataTable(); Cases.Columns.Add("CaseNumber"); Cases.Columns.Add("CreatedDate"); Cases.Columns[1].DataType = System.Type.GetType("System.DateTime"); DataRow dr; // dr = Cases.NewRow(); dr["CaseNumber"] = "One"; dr["CreatedDate"] = "12/12/2007"; Cases.Rows.Add(dr); // dr = Cases.NewRow(); dr["CaseNumber"] = "One"; dr["CreatedDate"] = "12/9/2007"; Cases.Rows.Add(dr); // dr = Cases.NewRow(); dr["CaseNumber"] = "Two"; dr["CreatedDate"] = "3/12/2008"; Cases.Rows.Add(dr); // dr = Cases.NewRow(); dr["CaseNumber"] = "Two"; dr["CreatedDate"] = "2/29/2008"; Cases.Rows.Add(dr); // IOrderedEnumerable> Query = from Case in Cases.AsEnumerable() orderby Case.Field("CaseNumber") ascending, Case.Field("CreatedDate") ascending group Case by Case.Field("CaseNumber") into CaseGroup orderby CaseGroup.Key select CaseGroup; foreach (IGrouping g in Query) Console.WriteLine(g.Key + " " + g.ElementAt(0).Field("CreatedDate"));
naura pax17y ago
hi,
you can sort and filter using datatable's select method which returns a datarow array:
DataRow[] drArr=dt.Select("OrderNo='" + txtOrderNo.Text + "'","[CreateDate] Desc");
// There is a single quote after OrderNo= and in third string in first argument, //second argument is optional.
DataTable dtNew=new DataTable();
for(int i=0;idtNew.Rows.Add(drArr[i].ItemArray)