Union of two tables but show which table the data came from

Question

I have two tables:

           TABLE_A                 TABLE_B
Fields:    Trans   Amend           Trans   Amend 
data:      100       0             100      0
           100       1
           110       0         
                                   120      0
                                   120      1
           130       0             130      0 
                                   130      1
           140       0             140      0
           150       0             150      0
           150       1             150      1
           150       2             

What I want is a table (view) that will combine (union) these to tables but will only show the highest Amend for each Trans

Looking for this as the answer:

Fields:    Trans   Amend   
data:      100       1
           110       0
           120       1
           130       1
           140       0 
           150       2   

Then to make it harder, I would like to know if there is a way I can tell from which table the data is coming from. Table A always wins when Record A and Record B are equal Looking for this as the answer:

Fields:    Trans   Amend    WhichTBL
data:      100       1      Table_A
           110       0      Table_A
           120       1      Table_B
           130       1      Table_B
           140       0      Table_A
           150       2      Table_A

I know a UNION can't be done to get this result.

Answer 1

would this work?

SELECT 
    trans, MAX(max_amend) as max_max_amend
FROM
    (SELECT
        'a' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_a
    GROUP BY
        trans

    UNION ALL

    SELECT
        'b' AS src, trans, MAX(amend) AS max_amend
    FROM
        table_b
    GROUP BY
        trans) m
GROUP BY
    trans

---

Lucero's point below is correct, the min(src) would be on the global set, not the related max()

I think you'd have to combine the source and table values into one column you can max. In your example, adding 1 to the value is all you need to distinguish the sources, like:

SELECT trans, Max(amend) AS MaxOfamend, 1+[amend] AS isa, 0 AS isb
FROM TableA
GROUP BY trans

but you could add 100, or multiply by a big value, or whatever works with your data. The idea is to combine the two pieces of information, the amend value and the source, into one column.

Then, after the information is combined, you get the max of that value, then strip off the source flag by uncombining them (subtracting 1, dividing by 100, whatever)

---

OK, here's what I got:

CREATE VIEW [dbo].[viewA]    AS
SELECT trans, MAX(amend + .20) AS srcIsA, 0 AS srcIsb
FROM  dbo.tableA
GROUP BY trans

CREATE VIEW [dbo].[viewB]    AS
SELECT trans, 0 AS srcIsA, MAX(amend + .10) AS srcIsB
FROM  dbo.tableB
GROUP BY trans

CREATE VIEW [dbo].[viewU]    AS
SELECT * from viewA
union all
select *
FROM  viewb

CREATE VIEW [dbo].[viewv]    AS
SELECT trans, srcIsA, srcIsb, srcIsA + srcIsb AS total
FROM  dbo.viewU

CREATE VIEW [dbo].[vieww]    AS
SELECT trans, MAX(total) AS max_total
FROM  dbo.viewv
GROUP BY trans

CREATE VIEW [dbo].[viewx] AS
SELECT trans, 
max_total, 
CAST(max_total AS int) AS maxval, 
CASE WHEN (max_total - CAST(max_total AS int)) = .1 THEN 'a' ELSE 'b' END AS src
FROM  dbo.vieww