sql - Getting most recent date values where other column values change

I have a table that is as follows:

  id value  date

  ---------------
  a, test,  01-01-15
  a, test,  01-02-15
  a, test,  01-03-15
  a, test1, 01-04-15
  a, test1, 01-05-15
  b, test, 01-01-15
  b, test, 01-02-15 

I need to write a query to be able to grab the latest mapping for both a & b

for instance, I want my results to be as follows:

a, test1, 01-05-15
b, test,  01-02-15

I can't seem to wrap my head around how to accomplish this

select max(date) 

only returns the latest date value

and select max(date), value group by value obviously does not return the id column

Answer

Depending on what version of SQL you use, you may have a function called ROW_NUMBER() OVER() that can help.

WITH x AS

(SELECT id, value, date 
        , ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM table
)
SELECT id, value, date
FROM x
WHERE RowNum = 1

The above would work in SQL Server and anything else that supports CTE and Windowing Functions. You could also write it as a derived table like this:

SELECT id, value, date
FROM
(SELECT id, value, date
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM table
) AS x
WHERE RowNum = 1