How to group rows and select latest date to display in a data table

Question

What are some ways I can sort the data table in the first image to look like it does in the second?

When the values of dosage, drug, and patient are the same for two or more rows, I want to merge them and use the newest date.

Answer 1

Without going to deep into actual C# code, I would recommend not focusing on how to manipulate the data table, but instead writing the proper query to get the information you want.

You can use a GROUP BY clause to group by dosage, drug, and patient, and use MAX() in your select statement to get the latest date for that group. You can also sort by the date in ascending order to match the layout you have. Try this:

SELECT dosage, drug, patient, MAX(date) AS date
FROM myTable
GROUP BY dosage, drug, patient
ORDER BY date ASC;

Here is an SQL Fiddle example for you.