locked
Fetch rows that have same value in a column RRS feed

  • Question


  • Hi,

    I have a datatable to store the User ids and Exam score details.
    Data table has four columns. Row Id (unique), UserId, Exam Id and Score. 

    Same user can take the same exam multiple times and can have different scores in each attempt.

    I want to fetch rows that have the same user id and exam Id and the score obtained in each attempt.

    For example, UserA takes Exam1 four times. Table will have 4 rows with UserA and Exam1. But the score will be different for each attempt.
    I want to get the score for all the 4 attempts  and get the maximum score out of four.

    UserX takes Exam2 ten times. I want to get the score obtained in all the ten attempts by UserX.

    How to filter the rows in a table that has same User Id and Exam ID using LINQ? And loop through the rows for each User id?

    Thanks
    Ashok

    Monday, October 29, 2012 1:48 PM

Answers

  • Ashok : You want to find the max score based on exam id ? If so see if the below helps

     public static void Main(string[] args)
            {
                DataTable objTable = new DataTable();
                objTable.Columns.Add("rowid", typeof(int));
                objTable.Columns.Add("userid", typeof(int));
                objTable.Columns.Add("examid", typeof(string));
                objTable.Columns.Add("score", typeof(int));
                DataRow objRow;
                objRow = objTable.NewRow(); objRow[0] = 1; objRow[1] = 1; objRow[2] = "CS101" ; objRow[3] = 71; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 2; objRow[1] = 2; objRow[2] = "CS101"; objRow[3] = 80; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 3; objRow[1] = 2; objRow[2] = "CS101"; objRow[3] = 67; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 4; objRow[1] = 1; objRow[2] = "CS101"; objRow[3] = 81; objTable.Rows.Add(objRow);
                var query = from row in objTable.AsEnumerable()
                            group row by row["userid"] into grp
                            select grp.OrderByDescending(a => a["score"]).First();

                foreach (var x in query)
                {
                    Console.WriteLine("{0}  {1}  {2}  {3}",x["rowid"],x["userid"],x["examid"],x["score"]     );
                }
            
            }

    • Proposed as answer by Norkk Tuesday, October 30, 2012 12:56 PM
    • Marked as answer by Ashok K Wednesday, October 31, 2012 7:24 AM
    Tuesday, October 30, 2012 12:22 PM
  • Hi Ashok,

    The following code snippet will do as your last post. This code uses the same DataTable schema as the post by Venkat786.

    var results = from row in objTable.AsEnumerable()
                  group row by new { UserId = row.Field<int>("UserId"), ExamId = row.Field<string>("ExamId")}
                      into examGroup
                  orderby examGroup.Key.UserId, examGroup.Key.ExamId
                  select new
                             {
                                 UserId = examGroup.Key.UserId,
                                 ExamId = examGroup.Key.ExamId,
                                 MaxScore = examGroup.Max(s => s.Field<int>("Score"))
                             };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Lisa ZhuModerator Wednesday, October 31, 2012 6:59 AM
    • Marked as answer by Ashok K Wednesday, October 31, 2012 7:24 AM
    Tuesday, October 30, 2012 2:12 PM

All replies

  • Ashok :

    Is this what you are looking for ?

      public static void Main(string[] args)
            {
                DataTable objTable = new DataTable();
                objTable.Columns.Add("rowid", typeof(int));
                objTable.Columns.Add("userid", typeof(int));
                objTable.Columns.Add("examid", typeof(string));
                objTable.Columns.Add("score", typeof(int));
                DataRow objRow;
                objRow = objTable.NewRow(); objRow[0] = 1; objRow[1] = 1; objRow[2] = "CS101" ; objRow[3] = 90; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 2; objRow[1] = 2; objRow[2] = "CS101"; objRow[3] = 90; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 3; objRow[1] = 1; objRow[2] = "CS101"; objRow[3] = 70; objTable.Rows.Add(objRow);
                var query = from row in objTable.AsEnumerable()
                            //where objRow.Field<int>("userid") == youruserid && objRow.Field<string>("examid") == "yourexamid"
                            where row.Field<int>("userid") == 1  && row.Field<string>("examid").Equals("CS101")
                            select row;

                foreach (var x in query)
                {
                    Console.WriteLine("{0}{1}{2}{3}",x["rowid"],x["userid"],x["examid"],x["score"]     );
                }
            
            } 
    Monday, October 29, 2012 3:00 PM
  • Hi

    Thanks for your response.  I want to calculate the max score for all the users in the table.

    I want to print the User id, Exam Id and Max score for all the users without passing any user id.

    Thanks
    Ashok

    Tuesday, October 30, 2012 7:52 AM
  • Ashok : You want to find the max score based on exam id ? If so see if the below helps

     public static void Main(string[] args)
            {
                DataTable objTable = new DataTable();
                objTable.Columns.Add("rowid", typeof(int));
                objTable.Columns.Add("userid", typeof(int));
                objTable.Columns.Add("examid", typeof(string));
                objTable.Columns.Add("score", typeof(int));
                DataRow objRow;
                objRow = objTable.NewRow(); objRow[0] = 1; objRow[1] = 1; objRow[2] = "CS101" ; objRow[3] = 71; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 2; objRow[1] = 2; objRow[2] = "CS101"; objRow[3] = 80; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 3; objRow[1] = 2; objRow[2] = "CS101"; objRow[3] = 67; objTable.Rows.Add(objRow);
                objRow = objTable.NewRow(); objRow[0] = 4; objRow[1] = 1; objRow[2] = "CS101"; objRow[3] = 81; objTable.Rows.Add(objRow);
                var query = from row in objTable.AsEnumerable()
                            group row by row["userid"] into grp
                            select grp.OrderByDescending(a => a["score"]).First();

                foreach (var x in query)
                {
                    Console.WriteLine("{0}  {1}  {2}  {3}",x["rowid"],x["userid"],x["examid"],x["score"]     );
                }
            
            }

    • Proposed as answer by Norkk Tuesday, October 30, 2012 12:56 PM
    • Marked as answer by Ashok K Wednesday, October 31, 2012 7:24 AM
    Tuesday, October 30, 2012 12:22 PM
  • Hi Ashok,

    The following code snippet will do as your last post. This code uses the same DataTable schema as the post by Venkat786.

    var results = from row in objTable.AsEnumerable()
                  group row by new { UserId = row.Field<int>("UserId"), ExamId = row.Field<string>("ExamId")}
                      into examGroup
                  orderby examGroup.Key.UserId, examGroup.Key.ExamId
                  select new
                             {
                                 UserId = examGroup.Key.UserId,
                                 ExamId = examGroup.Key.ExamId,
                                 MaxScore = examGroup.Max(s => s.Field<int>("Score"))
                             };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Lisa ZhuModerator Wednesday, October 31, 2012 6:59 AM
    • Marked as answer by Ashok K Wednesday, October 31, 2012 7:24 AM
    Tuesday, October 30, 2012 2:12 PM
  • Hi

    It is working fine now.. thank you very much.

    Thanks

    Ashok

    Wednesday, October 31, 2012 7:25 AM
  •  

    Not a problem Ashok, glad I was able to be of help.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, October 31, 2012 1:44 PM