Select Most Recent Entry in SQL

Question

I'm trying to select the most recent non zero entry from my data set in SQL. Most examples of this are satisfied with returning only the date and the group by variables, but I would also like to return the relevant Value. For example:

ID       Date          Value
----------------------------
001      2014-10-01     32
001      2014-10-05     10
001      2014-10-17      0
002      2014-10-03     17
002      2014-10-20     60
003      2014-09-30     90
003      2014-10-10      7
004      2014-10-06    150
005      2014-10-17      0
005      2014-10-18      9

Using

SELECT ID, MAX(Date) AS MDate FROM Table WHERE Value > 0 GROUP BY ID

Returns:

ID       Date      
-------------------
001      2014-10-05
002      2014-10-20
003      2014-10-10
004      2014-10-06
005      2014-10-18

But whenever I try to include Value as one of the selected variables, SQLServer results in an error:

"Column 'Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

My desired result would be:

ID       Date          Value
----------------------------
001      2014-10-05     10
002      2014-10-20     60
003      2014-10-10      7
004      2014-10-06    150
005      2014-10-18      9

One solution I have thought of would be to look up the results back in the original Table and return the Value that corresponds to the relevant ID & Date (I have already trimmed down and so I know these are unique), but this seems to me like a messy solution. Any help on this would be appreciated.

NOTE: I do not want to group by Value as this is the result I am trying to pull out in the end (i.e. for each ID, I want the most recent Value). Further Example:

ID       Date          Value
----------------------------
001      2014-10-05     10
001      2014-10-06     10
001      2014-10-10     10
001      2014-10-12      8
001      2014-10-18      0

Here, I only want the last non zero entry. (001, 2014-10-12, 8)

SELECT ID, MAX(Date) AS MDate, Value FROM Table WHERE Value > 0 GROUP BY ID, Value

Would return:

ID       Date          Value
----------------------------
001      2014-10-10     10
001      2014-10-12      8

Answer 1

This can also be done using a window function which is very ofter faster than a join on a grouped query:

select id, date, value
from (
  select id,
         date,
         value,
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;