Creating a rank column after UNION

Question

I'm attempting to create a SalesRank column based on the amount in column SalesAmount.

I'm attempting to do something similar to this: RANK() OVER(Order BY SalesAmount desc) as StateRank, but I am unsure on how to execute this after joining two tables together? How can I create another column after the union of both tables? In order to do this will I need to insert my union select into a #temptable?

Here is an example of my table:

StateId	ReportTitle	ReportId	SalesAmount
1	Online Sales in California	21	21512
12	Online Sales in New York	37	13201
14	Online Sales in Michigan	91	9212
23	Online Sales in Nevada	14	12931
8	Online Sales in Pennsylvania	14	23413
13	Online Sales in Oregon	14	9651

I am expecting to have a return like:

StateId	ReportTitle	ReportId	SalesAmount	SalesRank
1	Online Sales in California	21	21512	2
12	Online Sales in New York	37	13201	3
14	Online Sales in Michigan	91	9212	6
23	Online Sales in Nevada	14	12931	4
8	Online Sales in Pennsylvania	14	23413	1
13	Online Sales in Oregon	14	9651	5

Here is my query:

CREATE TABLE TableOne
(
    StateId INT,
    ReportTitle VARCHAR(100),
    ReportId INT,
    SalesAmount Money, 
)

INSERT INTO TableOne (StateId,ReportTitle,ReportId,SalesAmount)
VALUES (1,'Online Sales in California',21, 21512),(12,'Online Sales in New York',37,13201), (14,'Online Sales in Michigan',91,9212)

CREATE TABLE TableTwo
(
    StateId INT,
    ReportTitle VARCHAR(100),
    ReportId INT,
    SalesAmount Money, 
)

INSERT INTO TableTwo (StateId,ReportTitle,ReportId,SalesAmount)
VALUES (23,'Online Sales in Nevada',14,12931), (8,'Online Sales in Pennsylvania',14,23413), (13,'Online Sales in Oregon',14,9651)

SELECT * FROM TableOne 
UNION ALL 
SELECT * FROM TableTwo

Answer 1

You're almost there.

SELECT  *
        ,RANK() OVER (ORDER BY SalesAmount DESC) AS RowRank
FROM    (
            SELECT  *
            FROM    TableOne
            UNION ALL
            SELECT  *
            FROM    TableTwo
        ) AS t;