CODEWITHSUNDEEP
sqlsql-server
user3523142
user3523142

Reputation: 3

T-SQL query to get the last value in a list of months

Below is the data I have

date            value
30/03/2014  625949
23/03/2014  624549
16/03/2014  623149
09/03/2014  621549
02/03/2014  619749
23/02/2014  617749
16/02/2014  616149
09/02/2014  614549
02/02/2014  612949
19/01/2014  609749
12/01/2014  608149
06/01/2014  606749

if I want to get only the last value in a month, for example output like below, how I can get it?

date            value
30/03/2014  625949
23/02/2014  617749
19/01/2014  609749

Upvotes: 0

Views: 158

Answers (2)

huMpty duMpty
huMpty duMpty

Reputation: 14470

;with Cte as(Select tDate,value, C=ROW_NUMBER()
         over(PARTITION by convert(varchar(6), tdate, 112) order by tdate desc)
         From #Temp )

Select *
from cte
where C=1

Fiddle Sample

Upvotes: 5

Kaleel
Kaleel

Reputation: 321

SELECT date,value FROM table1 WHERE date in (
    SELECT MAX(date) FROM table1 GROUP BY MONTH(date)
)

Upvotes: 1

Related Questions