rdgbDhxm

1drop table if exists Table1;
2drop table if exists Table2;
3go
4
5create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
6create table Table2(Name varchar(9), Record int);
7go
8
9insert Table1 values ('Joy', 1), ('Joy', 2);
10insert Table2 values ('Joy', 0);
11go
12
13
14-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
15;with Data as
16(
17select
18    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
19from Table2  -- the order of queries within the CTE does not affect the output
20
21union all
22
23select
24    Name, Record, BlockNumber = 1
25from Table1
26)
27select 
28    Name, Record -- note that BlockNumber is not returned
29from Data
30order by
31    Name,           -- the leading column of the sort so one person's rows all appear together
32    BlockNumber,    -- will put Table2's row after Table1's.
33    Record;         -- since all Table1's rows are in the same block they'll sort by Record