Select MAX with the last 3 records per cliente 1

[andrecabrita]

Hi,

I'm trying to write a Query that the result is the same line the dates and values of the last 3 records per client, the date and value, of the typeDoc 15. Thank you very much

André Cabrita

Results
ClientID Name 1Last V1Last 2Last V2Last 3Last V3Last
1 ABC 20-01-2011 50 15-01-2011 90 10-01-2011 120
2 DEF 31-01-2011 33 22-01-2011 33 12-01-2011 25
3 GHI 22-01-2011 22 20-01-2011 45 20-01-2011 45
4....

Data
ClientID Date Name Value typeDoc
1 01-01-2011 ABC 100 14
1 10-01-2011 ABC 120 15
1 15-01-2011 ABC 90 15
1 20-01-2011 ABC 50 15
2 02-02-2011 DEF 15 15
2 12-01-2011 DEF 25 15
2 22-01-2011 DEF 55 15
2 31-01-2011 DEF 33 15
3 20-01-2011 GHI 45 15
3 22-01-2011 GHI 22 15
4....

 

[markros]

SQL 2005+:

;with cte as (select ClientID,[Name],[Date],[Value], row_number() over (partition by ClientID
order by [Date] Desc) as Row from [Data] where typeDoc = 15)

select ClientID,[Name], 
max(case when Row = 1 then [Date] end) as [1Last],
max(case when Row = 1 then [Value] end) as [V1Last],
max(case when Row = 2 then [Date] end) as [2Last],
max(case when Row = 2 then [Value] end) as [V2Last],
max(case when Row = 3 then [Date] end) as [3Last],
max(case when Row = 3 then [Value] end) as [V3Last]
from cte GROUP BY ClientID, [Name]

PluralSight Learning Library

 

[andrecabrita]

Hi Markros,

It works perfectly !!! Thank you so much !

Best Regards

Andre Cabrita