get first row for each group

Question

I want to transform this data

account; docdate; docnum
17700; 9/11/2015; 1
17700; 9/12/2015; 2
70070; 9/1/2015; 4
70070; 9/2/2015; 6
70070; 9/3/2015; 9

into this

account; docdate; docnum
17700; 9/12/2015; 2
70070; 9/3/2015; 9

.. for each account I want to have one row with the most current (=max(docdate)) docdate. I already tried different approaches with cross apply and row_number but couldn't achieve the desired results

Answer 1

Use ROW_NUMBER:

SELCT account, docdate, docnum
FROM (
  SELECT account, docdate, docnum,
         ROW_NUMBER() OVER (PARTITION BY account 
                            ORDER BY docdate DESC) AS rn
  FROM mytable ) AS t
WHERE t.rn = 1 

PARTITION BY account clause creates slices of rows sharing the same account value. ORDER BY docdate DESC places the record having the maximum docdate value at the top of its related slice. Hence rn = 1 points to the record with the maximum docdate value within each account partition.