SQL Query - get the latest document version URL for each document

Question

How can I list all documents displaying the latest version URL for each document?

NOTE. Versions of documents in which a document can have multiple versions. Versions are indicated by version numbers 1, 2, 3, etc.

I have the following sql but not sure if this is the most efficient way to do this.

select * 
from documents d
inner join documentVersions v on d.id = v.documentid
                              and v.id = (select top (1) v2.id 
                                          from documentversions v2 
                                          where v2.documentid = v.documentid 
                                          order by v2.version desc)

thank

Answer 1

Updated: According to your question: this is enough to satisfy your requirements.

    select t2.documentid,t1.name,max(t2.version) 
    from 
    documents t1 JOIN documentversions t2 
    on t1.id=t2.documentid group by t1.name,t2.documentid 
    order by t2.version,t2.createddate desc.

Answer 2

Try a hit request, this will return the latest version of each document.

Select  *
from    documents d
    inner join  (
        select  *,
            ROW_NUMBER() over(partition by v.documentid order by v.version desc) as row_no
        from    documentVersions v
    ) as v2 on  d.id = v2.documentid and v2.row_no = 1

. : Document.ModifiedDate , DocumentVersions.CratedDate, DocumentVersions,

Select * 
from documents d
inner join documentVersions v
 on d.id = v.documentid
 and d.ModifiedDate = v.CratedDate

Answer 3

:

select * into #DocumentVersions from  (
select 1 DocumentId, 2 Version, '12 ' Url  union all
select 1 DocumentId, 1 Version, '31 ' Url  union all
select 1 DocumentId, 3 Version, '33 ' Url  union all
select 2 DocumentId, 9 Version, '11 ' Url  union all
select 2 DocumentId, 1 Version, '12 ' Url  union all
select 3 DocumentId, 1 Version, '31 ' Url  
) x
select * into #documents from  (
           select 1 id  union all
            select 2 id  union all
             select 3 id 
) y
Select  *
from    #documents documents
    left join  (
        select  *,
            ROW_NUMBER() over(partition by documentid order by version desc) as top_row
        from    #documentVersions
    ) as documentVersions on  documents.id = documentid 
    where top_row = 1

Answer 4

ModifiedDate Documents ( ) VersionID, ID DocumentsVersions.

, . OUTPUT, SCOPE_IDENTITY() - ID () DocumentsVersions - Documents .

We use columns VersionIDwhere we have documents that are constantly updated. Having such a column will simplify your query and improve performance since you are going to execute INNER JOIN:

SELECT *
FROM Documents A
INNER JOIN DocumentsVersions B
    ON A.[VersionID] = B.[ID];