Select rows with most recent CreatedDate

Question

I have a table in (SQL Server 2014 with the following columns:

Type   ProjectNr    CreatedDate   Subject    Body
------------------------------------------------------------------------
101    1000         31-01-2017    12345      Lorem ipsum dolor sit amet
104    1000         31-01-2017    23456      Lorem ipsum dolor sit amet
109    1000         31-01-2017    34567      Lorem ipsum dolor sit amet
109    1000         31-09-2017    45678      Lorem ipsum dolor sit amet
109    1000         31-11-2017    56789      Lorem ipsum dolor sit amet
109    1001         31-10-2017    67890      Lorem ipsum dolor sit amet

I only need all type "109" with the latest CreateDate per ProjectNr

This is the needed result:

 Type   ProjectNr    CreatedDate   Subject    Body
 -----------------------------------------------------------------------
 109    1000         31-11-2017    56789      Lorem ipsum dolor sit amet
 109    1001         31-10-2017    67890      Lorem ipsum dolor sit amet

The ordering of the rows is not necessary

Answer 1

Give a row number using dense_rank or row_number function for each row group by / partition by Type and ProjectNr and in the descending order of CreatedDate columns. Then select the rows having row number 1.

Query

;with cte as(
    select [rn] = dense_rank() over(
         partition by [Type], [ProjectNr]
         order by [CreatedDate] desc
    ), *
    from [your_table_name]
    where [Type] = 109
)
select [Type], [ProjectNr], [CreatedDate], [Subject], [Body]
from cte
where [rn] = 1;