only return top row for each identifier in joins

[jwhitf4770]

cte_num

as

(

select distinct num from cte_PRB157

)

 

SELECT b.*,c.*, (b.Count - c.Count) as 'increase'

FROM cte_num

left JOIN cte_PRB157 as b on cte_num.Num = b.Num

left join cte_PRB157 as c on b.num = c.num and b.Idx <> c.idx

where b.DateTime > @eom

 

ORDER BY cte_num.Num, b.datetime desc

 

However my select then turns into multiple rows for num. I only want the top most row per num.

160697 D1001 799 2013-12-02 00:00:00.000 PRB157 150641 D1001 778 2013-10-31 00:00:00.000 PRB157 21160697 D1001 799 2013-12-02 00:00:00.000 PRB157 196836 D1001 770 2013-09-01 00:00:00.000 PRB157 29160703 D1002 16888 2013-12-02 00:00:00.000 PRB157 150647 D1002 16949 2013-10-31 00:00:00.000 PRB157 -61160703 D1002 16888 2013-12-02 00:00:00.000 PRB157 196835 D1002 18770 2013-09-01 00:00:00.000 PRB157 -1882

 

I know I'm missing something simple

[justsomeguy]

You could probably do something like where num = max(num).