Get max column with group by

Question

Hi,

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

Answer 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
        )

Answer 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

Answer 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
)

Answer 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