Select Highest Revision

Question

Have a table linked from accounting software, BOMLIST, that provides the bills of material for assembly items.  The table includes the following fields:  AssemblyItem / RevisionNumber / Effective Date / ComponentItem / QtyRequired.  The RevisionNumber default is 0 and there is no date populated initially.  If there is a change to the BOM, it adds additional records with the next sequential RevisionNumber and populates the Effective Date.

I need to most effective / efficient way to select the records that are part of the highest revision number only so they can be used for a production router.

Answer 1

ciao DRSCPA,

what about the previous 3d :

https://social.msdn.microsoft.com/Forums/office/en-US/bf07da70-59a1-4fcb-a24d-8f8861ee69bc/form-criteria?forum=accessdev

fixed the matter? 

for this one I would suggest two ways :

SELECT T.AssemblyItem ,

           T.ComponentItem ,

           T.QtyRequired, T.RevisionNumber ,

            T.[Effective Date]
FROM

         yourTable AS t
inner join

          ( select

                   max(RevisionNumber ) as MaxR

           from 

                      yourTabele ) as T1

             on

                 t.RevisionNumber =t1.MaxR

              or t.[Effective Date] is null;

-----------------------------------------------------------------------------------------

SELECT T.AssemblyItem ,

             T.ComponentItem ,

             T.QtyRequired,

             T.RevisionNumber ,

            T.[Effective Date]
FROM

          YourTable AS t
WHERE

          t.RevisionNumber =( select

                                         max(t1.RevisionNumber ) as MaxDateR

                              from

                                         yourTable as t1

                             where

                                      t1.AssemblyItem =T.AssemblyItem)
                            or

                                 t.[Effective Date] is null;;

first one should run faster than the second, usualluy join is faster then a correlated subQuery

Replace "yourTable" with the real name of the table of your scenario.

HTH

Ciao, Sandro.

Answer 2

Hi DRSCPA,

I try to make a test with suggestion given by Sandro Peruz.

I find that the first query posted by him can solve your issue.

below is testing result with his query.

Output:

if you think that this is your desired output then I suggest you to mark the suggestion given by Sandro Peruz as answer.

if you have any further question then let us know about that.

We will try to provide you further suggestions to solve the issue.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi DRSCPA,

Is your issue resolved?

I find that, After creating this thread, You did not done any follow up on this thread.

If your issue is solved then I suggest you to share your solution and mark it as an answer.

If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

If then after you have any further questions then you can let us know about it.

We will try to provide further suggestions to solve it.

I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

So take appropriate steps to close this thread , If your issue is resolved.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].