Getting top row for each unique group in the inner joined group query

table 1

id    name   class
1     ab     A
2     cd     A
3     ef     B    
4     ab     B
5     cd     B

table 2

name   test   marks
ab     1      90
ab     2      70
cd     2      80
cd     3      85
ef     3      85
ef     4      60

Hi, I have 2 t开发者_JS百科ables above, my question is what is the most efficient/best or simplest way to get the highest marks from table 2 for each person and join to table 1 such that returns:

id   name   class    [highest marks]
1    ab     A        90
2    cd     A        85
3    ef     B        85

Assuming SQL Server 2005+, using analytic/ranking/windowing functionality:

WITH example AS (
  SELECT a.id,
         a.name,
         a.class,
         b.marks,
         ROW_NUMBER() OVER(PARTITION BY a.id
                               ORDER BY b.marks DESC) AS rank
    FROM TABLE_1 a
    JOIN TABLE_2 b ON b.name = a.name)
SELECT e.id,
       e.name,
       e.class,
       e.marks
  FROM example e
 WHERE e.rank = 1

Using aggregates:

SELECT a.id,
       a.name,
       a.class,
       b.marks      
  FROM TABLE_1 a
  JOIN (SELECT t.name,
               MAX(t.mark) AS max_mark
          FROM TABLE_2
      GROUP BY t.name) b ON b.name = a.name

Another option if you dont want to use CTE (Common Table Expressions)

SELECT table1.id, table1.name, table1.class, MAX(table2.marks) AS [highest marks]
FROM table1 INNER JOIN
table2 ON table1.name = table2.name
GROUP BY table1.id, table1.name, table1.class