0

I have two tables.

DOCUMENT and REVISION

I want to make a list of all documents and show the latest revision. It works fine to find MAX(revision.autonumber) but as soon as I do inner join I no longer get the correct result. I then get not only the MAX(revision.autonumber) but also earlier revisions.

Thanks for your help

I have tried the query below (and many other attempts)

select
document.ItemId,
document.Name,
Max(revision.autonumber) 

from document

inner join 
revision on document.ItemId = revision.itemid 


group by
document.ItemId,
document.Name,
revision.doctitle

Image1 Image2

Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71
Andreas Nielsen
  • 21
  • 1
  • 4
  • Thank for your reply - Microsoft Dynamics AX – Andreas Nielsen Oct 28 '20 at 20:45
  • What version of AX? Are these custom tables in AX or base ones? If they're base, what are their actual names? – Alex Kwitny Oct 29 '20 at 02:07
  • Is this even a X++ query? `inner` is [not a X++ keyword](https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-syntax). If you need a SQL query solution, then have a look a [this](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4ed93bb6d175a3011fb67c154e92fe98). Do not forget to add the `Partition` and `DataAreaId` fields to enable index usage. – Sander Oct 29 '20 at 08:25

1 Answers1

0

You can create a view on revision containing only Max(revision.autonumber)

Then do the join between the view and the documenttable.

This will work in Axapta 3.0 and up.

Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50