# Thread: get max month and year from number

1. Registered User
Join Date
Sep 2009
Posts
62

## Unanswered: get max month and year from number

Hi,

i have a table that stores month and year in 2 seperate fields as a number, ie month = 12
year = 2010

month = 01
year = 2011

i need to be able to get the max month and year, but if i just say select max(month) i get 12 instead of 01 (01 is greater in this instance becasue of the year portion. Any way to do this using t-sql?

thanks

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```SELECT *
FROM (SELECT number AS m
FROM master.dbo.spt_values
WHERE 'P' = type
AND number BETWEEN 1 AND 12) AS mm
CROSS JOIN (SELECT 1900 + number AS y
FROM master.dbo.spt_values
WHERE 'P' = type) AS yy
ORDER BY CAST(Cast(y AS VARCHAR) + '-'
+     CAST(m AS VARCHAR) + '-01' AS DATETIME)```
-PatP

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by scabral7
i need to be able to get the max month and year
Code:
```SELECT year
, MAX(month)
FROM daTable
WHERE year = ( SELECT MAX(year) FROM daTable )
GROUP
BY year```

4. Registered User
Join Date
Nov 2004
Posts
1,428
Code:
```with CTE (Year_, Month_, RowNum)
AS
(SELECT Year_, Month_,
ROW_NUMBER() OVER (PARTITION BY Year_ ORDER BY Month_ DESC) as RowNum
FROM DaTable
)
SELECT Year_, Month_
FROM CTE
WHERE CTE.RowNum = 1```
I glued an underscore to Year and Month, to make it clear those are not the reserved words.

Edit: I should have read the question better. The previous code gives the maximum month for every years. Not the last month of the last year.
That's what this code does:
Code:
```with CTE (Year_, Month_, RowNum)
AS
(SELECT Year_, Month_,
ROW_NUMBER() OVER (ORDER BY Year_ DESC, Month_ DESC) as RowNum
FROM DaTable
)
SELECT CTE.*
FROM CTE
WHERE CTE.RowNum = 1```
Last edited by Wim; 03-03-11 at 08:50.

5. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
I figured I'd add one too. More along the lines of Keep It Simple, Stupid(KISS)

Code:
```select max(digits(year_) || digits(month_))
from my_table```
Dave Nance

6. Registered User
Join Date
Nov 2004
Posts
1,428
... More along the lines of Keep It Simple, Stupid(KISS)
The art is to make it as simple as it can get, not simpler.

This query will give the maximum year and the maximum month, not the maximum month of the maximum year.

On the data
Year Month
2009 12
2010 01

It will not return 2010 01, but 2010 12.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•