Tutorial :Get max column with group by



Question:

I have a table for contents on a page. The page is divided into sections. I want to get the last version for each page-section.

Id (int) Version (int) SectionID

  Id    Version    SectionID    Content  1       1           1           AAA  2       2           1           BBB  3       1           2           CCC  4       2           2           DDD  5       3           2           EEE  

I want to get:

  Id    Version    SectionID    Content  2       2           1           BBB  5       3           2           EEE  


Solution:1

You could use an exclusive self join:

select  last.*  from    YourTable last  left join          YourTable new  on      new.SectionID = last.SectionID          and new.Version > last.Version  where   new.Id is null  

The where statement basically says: where there is no newer version of this row.

Slightly more readable, but often slower, is a not exists condition:

select  *  from    YourTable yt  where   not exists          (          select  *          from    YourTable yt2          where   yt2.SectionID = yt.SectionID                  and yt2.Version > yt.Version          )  


Solution:2

Example table definition:

declare @t table(Id int, [Version] int, [SectionID] int, Content varchar(50))    insert into @t values (1,1,1,'AAA');  insert into @t values (2,2,1,'BBB');  insert into @t values (3,1,2,'CCC');  insert into @t values (4,2,2,'DDD');  insert into @t values (5,3,2,'EEE');  

Working solution:

select A.Id, A.[Version], A.SectionID, A.Content  from @t as A  join (      select max(C.[Version]) [Version], C.SectionID      from @t C      group by C.SectionID  ) as B on A.[Version] = B.[Version] and A.SectionID = B.SectionID  order by A.SectionID  


Solution:3

A simpler and more readeable solution:

select A.Id, A.[Version], A.SectionID, A.Content  from @t as A  where A.[Version] = (      select max(B.[Version])      from @t B      where A.SectionID = B.SectionID  )  


Solution:4

I just saw that there was a very similar question for Oracle with an accepted answer based on performance.

Maybe if your table is big, an performance is an issue you can give it a try to see if SQL server also performs better with this:

select Id, Version, SectionID, Content  from (      select Id, Version, SectionID, Content,             max(Version) over (partition by SectionID) max_Version      from   @t  ) A  where Version = max_Version  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »