Fetch rows that have same value in a column

[ashok.k]

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

 

[IanRyder]

Hi,

I see that you are using VS2005 and .NET2 so my example may be slightly different than what you are using. You mention that you are using a DataTable but in this example I am using a DataSet to store data. Regardless of how you access your data please see below two examples of using LINQ to answer your two questions;-

I am using the Northwind Database to provide these examples.

1) Get a max value in a range of records:-

Dim MaxCustomerID As Integer = (From OrderRec In NORTHWINDDataSet.Orders Where OrderRec.CustomerID = "ALFKI" And OrderRec.ShipCountry = "Germany" Select OrderRec.EmployeeID).Max
MsgBox(MaxCustomerID)

2) Get a list of results from a LINQ query and loop through them:-

Dim lstOrders As EnumerableRowCollection(Of NORTHWINDDataSet.OrdersRow) = (From OrderRec In NORTHWINDDataSet.Orders Where OrderRec.CustomerID = "ALFKI" Select OrderRec)
 
For Each Order In lstOrders
  MsgBox(Order.CustomerID)
Next

Hope that helps.

Cheers,

Ian