How to sort rows after union I have two tables that I want to combine.



I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the last record. 

Result should be:



I am new to SQL and with my research, it seems I need to use UNION to join both tables and to join the new table (from Union) by itself.  When I tried doing that, it created duplicate rows and columns.
You are correct - if you have two datasets with identical columns then UNION is the correct way to append the second dataset onto the first. Moreover I would suggest you use UNION ALL. UNION by itself guarantees unique values in the output. Usually this requires a sort and further processing to detect and eliminate duplicates. In your case I do not think you have duplicates so UNION ALL would be appropriate.

The real problem seems to be the sort order. You want the combined dataset to be sorted by Record but with 0 as the last item. The rows exist in two blocks: the zero rows and the other rows. If we assign a "block number" to each they can be sorted accordingly. Clearly this cannot be achieved by changing Record itself so another column must be introduced. This new artificial column need not be returned form the query.

You don't mention which DBMS you use. I have SQL Server to hand; I'll use it. For future questions please do not include screen shots of the data. I makes it much too hard to reproduce your situation. Instead include in the quesiton the CREATEs and INSERTs required to reproduce the test cases and also what queries you've tried and why the don't work. See mcve. I've written the minimum to demonstrate the proposed solution.

drop table if exists Table1;
drop table if exists Table2;
go

create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
create table Table2(Name varchar(9), Record int);
go

insert Table1 values ('Joy', 1), ('Joy', 2);
insert Table2 values ('Joy', 0);
go


-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
;with Data as
(
select
    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
from Table2  -- the order of queries within the CTE does not affect the output

union all

select
    Name, Record, BlockNumber = 1
from Table1
)
select 
    Name, Record -- note that BlockNumber is not returned
from Data
order by
    Name,           -- the leading column of the sort so one person's rows all appear together
    BlockNumber,    -- will put Table2's row after Table1's.
    Record;         -- since all Table1's rows are in the same block they'll sort by Record