select record based on max date

[scottcabral1979]

Hi,

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

 

[imex]

Hi,

Try:

with CTE as
(
    select *, ROW_NUMBER() OVER(PARTITION BY Occ_Num, Feature_NUM ORDER BY Trans_Date DESC) AS RowNum
    from MyTable
)

select * from CTE
where RowNum = 1

Hope this helps.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[markros]

Or check your MSDN thread where you got the same solution.

PluralSight Learning Library