Distinct over several tables-CodePudding

I have the below tables:

Corporate table

CorporateId    DocumentType    DocumentId     DocumentDate (dd/MM/YYYY)
  1              1              1               11/12/2022
  2              2              2               11/12/2022
  3              3              4               11/12/2022

Employee table

EmployeeId    DocumentType    DocumentId      DocumentDate (dd/MM/YYYY)
  1              1              7               15/12/2022
  2              4              3               15/12/2022
  3              5              5               15/12/2022

Car table

CarId       DocumentType    DocumentId      DocumentDate (dd/MM/YYYY)
  1              1              6               10/12/2022
  2              4              8               10/12/2022
  3              6              9               10/12/2022

I want to retrieve the latest document Id (based on the latest date) for document type 1 from all the tables above. That is for document type 1, it will be the document Id 7 which will be displayed.

I know I can do a union of all the three tables, then a distinct. But I fear this might not be optimal.

Any better way of doing it? Thanks

CodePudding user response：

You can get the union of your records into a CTE, then get the latest record that matches your condition.

;with cte as
(
Select  DocumentType,
        DocumentId,
        DocumentDate
from Corporate
Union
Select  DocumentType,
        DocumentId,
        DocumentDate
from Employee
Union
Select  DocumentType,
        DocumentId,
        DocumentDate
from Car
)
Select top 1 DocumentId
from cte
where DocumentType = 1
order by DocumentDate desc

CodePudding user response：

You may want to consider if anything else in the schema can help support this. But, based on what you have listed, you could do a TOP(1) from each table ordered by your date and matching the correct TypeID, UNION each of those, and then select based on an ordered RANK; for example, below:

DECLARE @A TABLE (TypeID INT, ID INT, DateVal DATE)
DECLARE @B TABLE (TypeID INT, ID INT, DateVal DATE)
DECLARE @c TABLE (TypeID INT, ID INT, DateVal DATE)
INSERT INTO @A VALUES (1,2,'1/1/2022'),(2,4,'1/2/2022'),(5,7,'1/3/2022')
INSERT INTO @B VALUES (1,3,'2/1/2022'),(4,5,'2/2/2022'),(5,8,'2/3/2022')
INSERT INTO @C VALUES (1,9,'3/1/2022'),(3,11,'3/2/2022'),(5,15,'3/3/2022')

;WITH TopFromEachTable AS
(
    SELECT  aSrc.Src,aSrc.TypeID,aSrc.ID,aSrc.DateVal 
    FROM  ( SELECT CAST(1 AS INT) [TypeID] ) pin
    CROSS APPLY (SELECT TOP (1) 'A' [Src],TypeID,ID,DateVal FROM @A a WHERE a.TypeID = pin.TypeID ORDER BY DateVal DESC) aSrc

    UNION ALL

    SELECT  bSrc.Src,bSrc.TypeID,bSrc.ID,bSrc.DateVal 
    FROM  ( SELECT CAST(1 AS INT) [TypeID] ) pin
    CROSS APPLY (SELECT TOP (1) 'B' [Src],TypeID,ID,DateVal FROM @B b WHERE b.TypeID = pin.TypeID  ORDER BY DateVal DESC) bSrc

    UNION ALL

    SELECT  cSrc.Src,cSrc.TypeID,cSrc.ID,cSrc.DateVal 
    FROM  ( SELECT CAST(1 AS INT) [TypeID] ) pin
    CROSS APPLY (SELECT TOP (1) 'C' [Src],TypeID,ID,DateVal FROM @C c WHERE c.TypeID = pin.TypeID ORDER BY DateVal DESC) cSrc
)
, RankedResults AS
(
SELECT
    t.Src,
    t.TypeID,
    t.ID,
    t.DateVal,
    DENSE_RANK() OVER (PARTITION BY TypeID ORDER BY DateVal DESC) DateRank
FROM
    TopFromEachTable t
)
SELECT  RR.*
FROM    RankedResults RR
WHERE   RR.DateRank = 1

This considers that you may get two results of the most recent date, if it is the same in more than one table. This does not account for returning more than one result, if there are multiple records with the same max date, within the same table.

CodePudding user response：

I don't think you need anything more complicated than a union of the data sets and then an analytic partition to select the most recent date(s) from each group. Presumably you'd want all documentIds if they tied on date, if not add the tie-breaker to the ordering.

with alldocs as (
  select *, Dense_Rank() over(partition by DocumentType order by DocumentDate desc) rn
  from (
    select DocumentType, DocumentId, DocumentDate from corporate union all
    select DocumentType, DocumentId, DocumentDate from employee union all
    select DocumentType, DocumentId, DocumentDate from car
  )d
)
select  DocumentType, DocumentId
from alldocs
where rn = 1;