locked
find maximum month of maximum year in sqlserver RRS feed

  • Question

  • User-644330731 posted

    dear all,

    below is my data ,

    MON           YEAR
    01                2014
    07                2014
    12                2014
    03                2015 and expected outputin '03' '2015' . but i get 12,2015 how do i do this?

    Friday, November 6, 2015 3:42 AM

Answers

  • User-62323503 posted
    declare @tab table (month tinyint, year smallint )
    insert into @tab
    values (1, 2014), (7, 2014), (12, 2014), (3, 2015)
    
    select	top 1 * 
    from	@tab
    order by year desc, month desc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 6:25 AM
  • User77042963 posted
    Create table test (Mon varchar(2),[Year] int)
    Insert into test values('01',2014),('07',2014),('12',2014), ('03',2015) 
    Select  MOn,[Year]  from (
    Select MOn,[Year] 
    , Row_NUmber() Over( Order BY Cast(Mon+'-01-'+Cast([Year] as Char(4)) as Datetime) DESC) rn from test) t
    Where rn=1
     
     
    drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:26 AM

All replies

  • User1577371250 posted

    Hi,

     SELECT TOP 1  MAX([YEAR]), [MON] FROM [Table1] GROUP BY [YEAR], [MON] order by [YEAR] DESC

    Friday, November 6, 2015 3:55 AM
  • User-1716253493 posted
    SELECT MAX([MON]), [YEAR] FROM [Table1] GROUP BY [YEAR] WHERE [YEAR]=MAX(YEAR)

    Friday, November 6, 2015 4:05 AM
  • User-62323503 posted
    declare @tab table (month tinyint, year smallint )
    insert into @tab
    values (1, 2014), (7, 2014), (12, 2014), (3, 2015)
    
    select	top 1 * 
    from	@tab
    order by year desc, month desc

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 6:25 AM
  • User77042963 posted
    Create table test (Mon varchar(2),[Year] int)
    Insert into test values('01',2014),('07',2014),('12',2014), ('03',2015) 
    Select  MOn,[Year]  from (
    Select MOn,[Year] 
    , Row_NUmber() Over( Order BY Cast(Mon+'-01-'+Cast([Year] as Char(4)) as Datetime) DESC) rn from test) t
    Where rn=1
     
     
    drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:26 AM