Как получить верхнюю 1 из таблиц объединения

Для одной строки с последним :select top 1 * from ( select a.vendorid, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM20000 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') union all select a.VENDORID, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM30200 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') ) u order by [Last Date Received] desc

[Last Date Received]

---

Для последней VendorIdдля каждого top with tiesиспользования row_number()с :select top 1 with ties * from ( select a.vendorid, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM20000 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') union all select a.VENDORID, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM30200 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') ) u order by row_number() over (partition by VendorId order by [Last Date Received] desc)

[Last Date Received] desc

---

Используя общее табличное выражение и порядок :;with cte as ( select top 1 with ties * from ( select a.vendorid, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM20000 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') union all select a.VENDORID, a.DOCDATE as [Last Date Received], b.VENDNAME as [Vendor Name] from PM30200 a inner join PM00200 b on a.VENDORID = b.VENDORID where b.VNDCLSID in ('cb', '6') ) u order by row_number() over (partition by VendorId order by [Last Date Received] desc) ) select VendorId , VendorName , [Last Date Received] , Days_LastInvoiced = datediff(day,[Last Date Received],getdate()) from cte order by [Last Date Received] desc

select 
    VendorId
  , VendorName
  , [Last Date Received]
  , Days_LastInvoiced = datediff(day,[Last Date Received],getdate())
from (
  select top 1 with ties *
  from (
      select a.vendorid,
          a.DOCDATE as [Last Date Received],
          b.VENDNAME as [Vendor Name] 
      from PM20000 a
      inner join PM00200 b on a.VENDORID = b.VENDORID
      where b.VNDCLSID in ('cb', '6')
      union all
      select a.VENDORID,
          a.DOCDATE as [Last Date Received],
          b.VENDNAME as [Vendor Name] 
      from PM30200 a
      inner join PM00200 b on a.VENDORID = b.VENDORID
      where b.VNDCLSID in ('cb', '6')
    ) u
  order by row_number() over (partition by VendorId order by [Last Date Received] desc)
)
order by [Last Date Received] desc

---

без общего выражения таблицы ...

TOP

По соображениям производительности, я хотел бы предложить , что вы делаете , TOP прежде чем делать то UNION. , , и использовать UNION ALL:

select top 1 *
from ((select top 1 a.vendorid, max(a.DOCDATE) as [Last Date Received],
              max(b.VENDNAME) as [Vendor Name]
       from PM20000 a inner join
            PM00200 b
            on a.VENDORID = b.VENDORID
      where b.VNDCLSID in ('cb', '6')
      group by a.VENDORID
      order by [Last Date Received] desc
     )
     union all
     (select top 1 a.VENDORID, max(a.DOCDATE) as [Last Date Received],
             max(b.VENDNAME) as [Vendor Name] 
      from PM30200 a inner join
           PM00200 b
           on a.VENDORID = b.VENDORID
      where b.VNDCLSID in ('cb', '6')
      group by a.VENDORID
      order by [Last Date Received] desc
    )
   ) tt
order by [Last Date Received] desc;

Как указывает SQLZim, используйте, with tiesесли вы дублируете.