Select Most Recent Entry in SQL -

Select Most Recent Entry in SQL -

i'm trying select recent non 0 entry info set in sql. examples of satisfied returning date , grouping variables, homecoming relevant value. 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) mdate table value > 0 grouping 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 seek include value 1 of selected variables, sqlserver results in error:

"column 'value' invalid in select list because not contained in either aggregate function or grouping clause."

my desired result 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 have thought of results in original table , homecoming value corresponds relevant id & date (i have trimmed downwards , know these unique), seems me messy solution. help on appreciated.

note: not want grouping value result trying pull out in end (i.e. each id, want recent value). farther 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, want lastly non 0 entry. (001, 2014-10-12, 8)

select id, max(date) mdate, value table value > 0 grouping id, value

would return:

id date value ---------------------------- 001 2014-10-10 10 001 2014-10-12 8

assuming don't have repeated dates same id in table, should work:

select a.id, a.date, a.value t1 inner bring together (select id,max(date) date t1 value > 0 grouping id) b on a.id = b.id , a.date = b.date

sql select sql-server-2008-r2 greatest-n-per-group