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

Yes, you use SELECT INTO a temp table and then rank your results or just use a CTE. I'd recommend trying a CTE first, if performance is slow, then use a temp table — Stephan, May 25 '21 at 00:08
I'm not familiar with the term CTE. Could you please provide an example if possible? — kurtixl, May 25 '21 at 00:13
CTE = Common Table Expression. It's kind of like a temporary view. Microsoft doc https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 — Stephan, May 25 '21 at 00:16

score 1 · Accepted Answer · answered May 25 '21 at 00:09

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;

answered May 25 '21 at 00:09

Mike Petri

570
3
10

Dale K · Answer 2 · 2021-05-25T01:28:30.963

1

Or you can use a Common Table Expression (which in this case is just a neater/different way to write a sub-query, but does provide more benefits in other situations).

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

edited May 25 '21 at 01:28

answered May 25 '21 at 00:46

Dale K

25,246
15
42
71

Creating a rank column after UNION

2 Answers2