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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments