CODEWITHSUNDEEP
sql-servert-sqldense-rank
Joe
Joe

Reputation: 468

How to rank one data set using another in SQL?

I have a table with float values. I want to use a subset (A) of those values to create ranks using PERCENT_RANK(). Then I want to assign ranks to a second (non-intersecting) subset (B) of values in the table based on the ranks derived from the first subset (A). Simply joining on values from (B) to values in (A) won't work since the values from subset (B) in general won't equal values in subset (A). In that case, I'm fine using either a "closest value" approach or a "linear interpolation" approach to get the ranks. My preference is for speed and simplicity since I'm dealing with hundreds of thousands of rows.

Here is a concrete example (assume subset A is where Flag = 0 and subset B is where Flag = 1):

DECLARE @Data TABLE
(
  Value FLOAT,
  Flag BIT
)
INSERT INTO @Data SELECT 0.081, 0
INSERT INTO @Data SELECT 0.831, 0
INSERT INTO @Data SELECT 0.798, 0
INSERT INTO @Data SELECT 0.722, 0
INSERT INTO @Data SELECT 0.322, 0
INSERT INTO @Data SELECT 0.186, 0
INSERT INTO @Data SELECT 0.494, 0
INSERT INTO @Data SELECT 0.757, 0
INSERT INTO @Data SELECT 0.996, 0
INSERT INTO @Data SELECT 0.146, 0
INSERT INTO @Data SELECT 0.514, 1
INSERT INTO @Data SELECT 0.787, 1
INSERT INTO @Data SELECT 0.125, 1
INSERT INTO @Data SELECT 0.324, 1
INSERT INTO @Data SELECT 0.86, 1
--Subset A
SELECT *,
    Rnk = PERCENT_RANK() OVER (ORDER BY Value)
FROM @Data
WHERE Flag = 0
--Subset B
SELECT *,
    Rnk = ?--Ranking based on ranks derived from subset A
FROM @Data
WHERE Flag = 1

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Hmmm . . . This is one way:

with a as (
      select d.*
             PERCENT_RANK() OVER (ORDER BY Value) as rnk
      from @Data d
      where Flag = 0
     )
select b.*, a.rnk
from @Data b outer join
     (select top 1 *
      from a
      where a.value <= b.value
      order by a.value desc
     ) a
where Flag = 1;

Upvotes: 1

Related Questions