How to get max of composite data in SQL?

Question

SELECT "Name""Month","Year","Value" 
from Table
WHERE 
    "Name" LIKE '%JERRY%'
AND "Year" = 
    (SELECT MAX("Year") FROM Table where "Name" LIKE '%JERRY%')
AND "Month"= 
    (SELECT MAX("Month") FROM Table 
     where 
        "Name" LIKE '%JERRY%' 
    AND "Year"= (SELECT MAX("Year") FROM Table where "Name" LIKE '%JERRY%'))

Table -->

Name  | Year | Month | Value
-----------------------------
JERRY   2012    9        100           
JERRY   2012    9        120         
JERRY   2012    9        130           
JERRY   2012    8         20        
JERRY   2011    12        50

So i want the first three rows as output. As for the latest month for the latest year i need all the values. Can someone suggest a better cleaner query?

I don't understand your output requirements. Also -- the sample data should presumably contain another person's data rather than just JERRY's? — Transact Charlie, Oct 25 '12 at 11:27
CRATE TABLE #sample ( [Name] VARCHAR(255) , [Year] INT , [Month] INT , [Value] INT ) INSERT #sample ([Name], [Year], [Month], [Value]) VALUES ('JERRY', 2012, 9, 100) , ('JERRY', 2012, 9, 120) , ('JERRY', 2012, 9, 130) , ('JERRY', 2012, 8, 20) , ('JERRY', 2012, 12, 50) SELECT * FROM @sample'code' — Transact Charlie, Oct 25 '12 at 11:30

Niladri Biswas · Answer 1 · 2012-10-25T13:06:01.093

DECLARE @t Table(Name Varchar(30),[Year] Int, [Month] Int,Value Int)
Insert Into @t Values('JERRY' ,  2012,    9,        100 )
Insert Into @t Values('JERRY',   2012,    9 ,       120)         
Insert Into @t Values('JERRY' ,  2012,    9 ,       130)           
Insert Into @t Values('JERRY',   2012 ,   8 ,        20)        
Insert Into @t Values('JERRY',   2011,    12 ,       50) 


Declare @LatestYr Int 
Declare @LatestMonth Int

Select @LatestYr= Max([Year])From @t
Select @LatestMonth = Max([Month]) From @t Where [Year] = @LatestYr

Select * From @t
Where ([Year] = @LatestYr And [Month] = @LatestMonth)

Result

enter image description here

The above query will work just for a single user. And will fail for multiple users, or in case of ties. For example, consider the below scenario

enter image description here

In this case, the needed output will be

enter image description here

So for handling such a situation, I am proposing the below solutions

Solution 1

Select t.* 
From @t t
Join
(
    Select x.Name,x.Max_Year,y.Max_Month
    From
        (   SELECT Name,Max_Year = Max([Year])
            From @t
            Group By Name
        )x
    Join
        (   SELECT Name,[Year],Max_Month= Max([Month])
            From @t
            Group By Name,[Year]
        )y On x.Name = y.Name And x.Max_Year = y.[Year]
)x
On t.Name = x.Name 
And t.[Year] = x.Max_Year 
And t.[Month] = x.Max_Month

OR

Solution 2 (Sql Server 2005+)

Select Name,[Year],[Month],Value
From
(
    Select *,Rn = Rank() Over(Partition By Name Order By [Year] desc, [Month] Desc) 
    From @t
)X Where X.Rn =1

Solution 3 (Sql Server 2005+)

Select Name,[Year],[Month],Value
From
(
    Select *,Rn = Dense_Rank() Over(Partition By Name Order By [Year] desc, [Month] Desc) 
    From @t
)X Where X.Rn =1

The ddl is as under

DECLARE @t Table(Name Varchar(30),[Year] Int, [Month] Int,Value Int)
Insert Into @t Values('JERRY' ,  2012,    9,        100 )
Insert Into @t Values('JERRY',   2012,    9 ,       120)         
Insert Into @t Values('JERRY' ,  2012,    9 ,       130)           
Insert Into @t Values('JERRY',   2012 ,   8 ,        20)        
Insert Into @t Values('JERRY',   2011,    12 ,       50) 
Insert Into @t Values('FERRY' ,  2010,    9,        100 )
Insert Into @t Values('FERRY',   2010,    9 ,       120) 
Insert Into @t Values('FERRY',   2010,    8 ,       120) 
Insert Into @t Values('JERRY1' ,  2012,    9,        100 )
Insert Into @t Values('JERRY1',   2012,    9 ,       120)         
Insert Into @t Values('JERRY1' ,  2012,    9 ,       130)           
Insert Into @t Values('JERRY1',   2012 ,   8 ,        20)        
Insert Into @t Values('JERRY1',   2011,    12 ,       50)

Hope this may help. Thanks

Transact Charlie · Accepted Answer · 2012-10-25T13:32:49.793

2

If there are other people in the table then you probably want to use a ranking function: Something like:


DECLARE @sample TABLE (
      [Name] VARCHAR(255)
    , [Year] INT
    , [Month] INT
    , [Value] INT
    )

INSERT @sample ([Name], [Year], [Month], [Value])
VALUES ('JERRY', 2012, 9, 100)           
     , ('JERRY', 2012, 9, 120)
     , ('JERRY', 2012, 9, 130)
     , ('JERRY', 2012, 8, 20)
     , ('JERRY', 2011, 12, 50)
     , ('FRED', 2011, 12, 50)
     , ('FRED', 2011, 12, 120)
     , ('FRED', 2011, 7, 150)

SELECT *
FROM (
    SELECT *
         , RANK() OVER (PARTITION BY [Name] ORDER BY [Year] DESC, [Month] DESC) AS [rnk]
    FROM @sample
    )
    AS samp
WHERE
    samp.[rnk] = 1

Which gives results:

Name                      Year        Month       Value       rnk
------------------------- ----------- ----------- ----------- ------
FRED                      2011        12          50          1
FRED                      2011        12          120         1
JERRY                     2012        9           100         1
JERRY                     2012        9           120         1
JERRY                     2012        9           130         1

edited Oct 25 '12 at 13:32

answered Oct 25 '12 at 11:39

Transact Charlie

2,195
17
14

1

I like your answer but `ORDER BY [Year] * 100 + [Month] DESC` really? Why not `ORDER BY [Year] DESC, [Month] DESC` ? – ypercubeᵀᴹ Oct 25 '12 at 11:44
@ypercube, as per OP "So i want the first three rows as output. As for the latest month for the latest year i need all the values. " – Niladri Biswas Oct 25 '12 at 11:56
@Niladri: This gives the first rows (latest year,latest month) **per Name**. If the `WHERE "Name" LIKE '%JERRY%'` is included in the internal subquery, the result is exactly what the OP wants. – ypercubeᵀᴹ Oct 25 '12 at 11:58
The filtering is done by the `RANK()` function and the `WHERE samp.[rnk] = 1` – ypercubeᵀᴹ Oct 25 '12 at 11:59
Yes, I got your point.. you are right..in that case Partition By Name Order By [Year] desc, [Month] Desc is enough – Niladri Biswas Oct 25 '12 at 12:20

AnandPhadke · Answer 3 · 2012-10-25T11:30:05.087

1

select * from @t where
[Year] = (select max([year]) from @t) and 
[Month] = (select max([Month]) from @t where [Year]=(select max([year]) from @t))

edited Oct 25 '12 at 11:30

answered Oct 25 '12 at 11:22

AnandPhadke

13,160
5
26
33

@NiladriBiswas -- di dyou check the latest query? – AnandPhadke Oct 25 '12 at 11:34

score 0 · Answer 4 · answered Oct 26 '12 at 02:38

0

select * 
from    your_table
where   "Name" LIKE '%JERRY%' 
and     year*100+month 

in(
    select top 1 year*100+month 
    from your_table
    where 
    "Name" LIKE '%JERRY%' 
    order by cast(year*100+month as int) desc)

CLICK for demo

answered Oct 26 '12 at 02:38

user1577417

126
3

How to get max of composite data in SQL?

4 Answers4

CLICK for demo