asked
How to find the Highest Scores in a SQL Server Table
I have a table with some Columns (StudentID, StudentName, TestDate, TestScore). StudentID StudentName TestDate TestScore 1 Student1 01/23/2016 87 2 Student2 01/20/2016 80 3 Student3 01/23/2016 100 1 Student1 01/05/2016 92 1 Student1 03/12/2016 77 2 Student2 01/02/2016 91 3 Student3 02/11/2016 87 3 Student3 01/06/2016 95 4 Student4 01/27/2016 50 5 Student5 01/23/2016 85 2 Student2 04/04/2016 65 4 Student4 01/31/2016 92 4 Student4 02/17/2016 99 5 Student5 05/10/2016 85 5 Student5 01/08/2016 85 I need to write a Procedure (Stored?) to find the highest score for each student during a given period of time (for instance, the highest score for each Student between 01/01/2016 and 01/31/2016. I am passing the Starting and Ending Dates from another table (tblDates) which has just one record with Starting and Ending dates. Please help, Humberto Goez
Build it up a layer at a time. Start with a SELECT that returns all the rows. Then add a WHERE clause on TestDate to fiter which rows are returned. Then you can return the StudentID, with a MAX() on the TestScore and a GROUP BY on the StudentID and keep the WHERE clause. Now use CREATE PROC, give the proc a name, add the two date parameters, copy your query into the body, and change the WHERE clause to use those parameter values. At each stage, look up the new bit in the help system*, add the new bit, run the code and check how it's working. This is a great learning exercise. *Easiest way: type in the keyword(s), highlight that part, and press F1.
David, Thank you very much for taking the time to explain to me how to go about doing this. I will try it and let you know how it goes. Humberto Goez
20 People are following this question.
