1

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
Shay
  • 13
  • 2

4 Answers4

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
        )
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • The first solution doesn't work. I don't understand why your `where` says `id is null`? – Mariano Desanze Jul 22 '10 at 15:29
  • @Protron: missed an `and` in the join condition, added now. The `left join` searches for a row that's newer than the row in `last`. The where clause says that a newer row cannot exist (`new.Id` is null if the `left join` can't find a match.) – Andomar Jul 22 '10 at 15:44
  • Oh I didn't catch that mistake. Thanks for the explanation. – Mariano Desanze Jul 22 '10 at 16:00
  • I think a solution without temp tables is always better.. But then again, I'm not a DBA =) – Shay Jul 22 '10 at 16:04
1

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
Mariano Desanze
  • 7,847
  • 7
  • 46
  • 67
1

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
)
Mariano Desanze
  • 7,847
  • 7
  • 46
  • 67
  • This looks like the most 'normal' SQL way of doing this to me. Andomar's approach with relational operators is equally logical, but seems more fussy than just using max. Your query with an explicit join is basically the same as this, but this is more concise. The query you got from an Oracle guy which returns every row with a max_Version added is horrific - i don't doubt it works, but it offends my eyes! – Tom Anderson Jul 23 '10 at 17:00
0

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
Community
  • 1
  • 1
Mariano Desanze
  • 7,847
  • 7
  • 46
  • 67