# How to Return All Columns from a Table's Row When Calculating Aggregations

23719.zip

I have a table containing student exam grades. To create the Grades table and populate it with sample data, I can run the script that Listing 1 shows. Then, to get the highest exam grades for each student in each course, I can run the script that Listing 2 shows. Figure 1 contains the data that Listing 2 returns. However, I want to go one step further and retrieve whole rows from the Grades table, including the exam number (1st, 2nd, 3rd, and so on) and extra Grades table columns. (For the sake of simplicity, I didn't include the extra columns in the CREATE TABLE statement or output figures.) If a student has more than one exam in the same course with the highest grade, I want to use the exam number as a tiebreaker. In other words, I want to return the row with the most recent exam (highest exam number) out of the exams with the highest grade per student per course. How can I generate that output?

The second approach to solving this problem is to use joins instead of subqueries. As you'll recall, Listing 2 shows how to retrieve the highest grade for each combination of student and course. You can return the highest grade by turning Listing 3's query into a derived table and joining the resulting query to the original Grades table, as Listing 5 shows. Listing 5 returns more than one row for each student in each course when a course contains more than one row with a student's highest grade. The output is the same as in Figure 2. Listing 6 shows the query that returns the highest exam number for each combination of student, course, and grade. By turning the query that Listing 6 shows into a derived table and adding it to Listing 5's query, you produce Web Listing 1. Web Listing 1 joins the Grades table and the two derived tables to return the result that the reader seeks: the highest grades for the highest exam numbers per student per course. The output is the same as in Figure 3.

After applying the appropriate indexes, I compared the performance of the correlated subqueries and the joins solutions and found that the subqueries solution performed a bit better than the joins solution. Because the difference isn't significant, you can implement the solution that you feel more comfortable with.