sql - Stored procedure, left join gives me duplicate rows, but I want top(1) row for each record with same ID -

sql - Stored procedure, left join gives me duplicate rows, but I want top(1) row for each record with same ID -

i have stored procedure,

select top(1) tablea.*, tableb.*, somefunction(tablea.id), coalesce(tablec.somecolumn, -1) somecolumnexample tablea inner bring together tableb on tablea.id = tableb.ida left bring together tablec on tablea.id = tablec.ida tablea.id = @paraid order tablec.datetime desc

now getting results example,

ida somecolumn dateadded 1 100 2014-05-15 10:22:00 1 200 2014-05-10 12:00:00 2 300 2014-06-10 12:00:00 3 400 2014-07-10 12:00:00

what want (latest date)

ida somecolumn dateadded 1 200 2014-05-10 12:00:00 2 300 2014-06-10 12:00:00 3 400 2014-07-10 12:00:00

first looking not give me null columns left join, used,

coalesce(tablec.somecolumn, -1) somecolumnhehe

then came across issue needed select top 1 left join, used outer apply, shown below,

select top(1) tablea.*, tableb.*, somefunction(tablea.id), coalesce(tablec.somecolumn, -1) somecolumnexample, temptable.ida tablea inner bring together tableb on tablea.id = tableb.ida outer apply (select top(1) * tablec tablec.ida = tablea.ida order dateadded desc ) temptable tablea.id = @paraid order tablec.datetime desc

sql sql-server sql-server-2008