Selecting in SQL Server 2008

Question

I have table

ID       Name        Book   
1        Aaron       HTML
2        Charles     DESIGN
3        Mark        SQL
4        Charles     JAVA
5        Charles     C++ 
6        Mark        C#

I want to SELECT the rows with the greatest ID per Name, having a result is like this:

ID       Name        Book   
1        Aaron       HTML
5        Charles     C++ 
6        Mark        C#

Thanks in advance.

Answer 1

You can use ROW_NUMBER() function with a CTE:

;WITH CTE AS
(
   SELECT ID, Name, Book, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID DESC) RN
   FROM yourTable
)
SELECT ID, Name, Book
FROM CTE
WHERE RN = 1

Fiddle Demo

Answer 2

Quick and dirty, but should work:

select * from [mytable] where id in 
  (select max(id) as id from mytable group by name)