CODEWITHSUNDEEP
sqlsql-server
Mally
Mally

Reputation: 95

SQL order dates sequentially by year

I have a SQL view that produces the following list of Mondays in a specific date range as shown below:

Date        Number
16/12/2013  208
23/12/2013  190
30/12/2013  187
15/12/2014  203
22/12/2014  190
29/12/2014  153
14/12/2015  225
21/12/2015  217
28/12/2015  223

Is it possible to order them by the first of each year then the second then the third etc. to give me the results as shown below:

Date        Number
16/12/2013  208
15/12/2014  203
14/12/2015  225
23/12/2013  190
22/12/2014  190
21/12/2015  217
30/12/2013  187
29/12/2014  153
28/12/2015  223

Thank you in advance for any help or advice.

Upvotes: 0

Views: 65

Answers (1)

MightyRearranger
MightyRearranger

Reputation: 128

I think you should be able to get what you want by using the row_number() over a partition on the year, for example:

Select [Date], [Number], 
       Row_Number() over (PARTITION BY Year([DATE] order by [DATE]) as WEEK_IN_YR
  from [table]
order by WEEK_IN_YR, [Date]

https://msdn.microsoft.com/en-gb/library/ms186734.aspx

Upvotes: 2

Related Questions