Apply a conditional rank to a temporary table

Question

Here's an idea of what the temporary table contains:

ID	FullRank	Special
1	1	No
1	2	Yes
1	3	No
2	1	Yes
2	2	No
3	1	No
3	2	Yes
3	3	Yes
3	4	No
3	5	No

And this is what I'm trying to achieve...

ID	FullRank	Special	SpecialRank
1	1	No
1	2	Yes	1
1	3	No	2
2	1	Yes	1
2	2	No	2
3	1	No
3	2	Yes	1
3	3	Yes	2
3	4	No	3
3	5	No	4

Essentially, I want to insert another rank ('SpecialRank') that ranks from the first instance of 'Special' for an ID, based on the highest 'FullRank'.

I've tried variations of the following but without success:

UPDATE #SpecialTemp AS STa
INNER JOIN (SELECT MIN(STb.FullRank) STb.ID
FROM #SpecialTemp AS STb
GROUP BY STb.ID
) AS b
ON STa.ID = b.ID
 AND STa = 'Yes'
SET STa.SpecialRank = 'Yes'

I'm getting some syntax errors with this, and unsure if I'm taking the right approach with this. Thanks!

What RDBMS do you use? And why doesn't your first table contain the column SpecialRank? Anyway, this might help to get you started: db<>fiddle — Jonas Metzler
– Jonas Metzler, Commented Jul 11 at 12:14
Which SQL dialect and version are you using (e.g., SQL Server 2019, MySQL 8.0, PostgreSQL 16)? Temp-table naming (#SpecialTemp) and the UPDATE … JOIN syntax differ between platforms, so the exact solution depends on your DBMS. — Salt
– Salt, Commented Jul 11 at 12:16
@JonasMetzler I'm on SMSS, and sorry I probably should have added in SpecialRank containing null values against every record. But thanks very much, your method worked! — TheSteersman
– TheSteersman, Commented Jul 11 at 12:50
@TheSteersman Do you mean SSMS = SQL Server Management Studio? — Bart McEndree
– Bart McEndree, Commented Jul 11 at 12:58

Charlieface · Accepted Answer · 2025-07-11 14:19:57Z

You can use two levels of window functions. First a conditional running count to mark the first instance of Special = Yes, then a partitioned row-number to number all rows, splitting by whether they have passed Special = Yes.

WITH counted AS (
    SELECT *,
      CASE WHEN COUNT(CASE WHEN ST.Special = 'Yes' THEN 1 END)
        OVER (PARTITION BY ST.ID ORDER BY ST.FullRank ROWS UNBOUNDED PRECEDING) > 0
        THEN 1 ELSE 0 END AS PassedFirstSpecial
    FROM #SpecialTemp st
)
SELECT
  ST.ID,
  ST.FullRank,
  ST.Special,
  CASE WHEN PassedFirstSpecial = 1 THEN
    ROW_NUMBER() OVER (PARTITION BY ST.ID, PassedFirstSpecial ORDER BY ST.FullRank)
    END AS SpecialRank, PassedFirstSpecial
FROM counted ST
ORDER BY
  ID,
  FullRank;

To use this in an UPDATE, put the second level in a CTE as well and just update the CTE.

WITH counted AS (
    SELECT *,
      CASE WHEN COUNT(CASE WHEN ST.Special = 'Yes' THEN 1 END)
        OVER (PARTITION BY ST.ID ORDER BY ST.FullRank ROWS UNBOUNDED PRECEDING) > 0
        THEN 1 ELSE 0 END AS PassedFirstSpecial
    FROM #SpecialTemp st
),
numbered AS (
    SELECT *,
      CASE WHEN PassedFirstSpecial = 1 THEN
        ROW_NUMBER() OVER (PARTITION BY ST.ID, PassedFirstSpecial ORDER BY ST.FullRank)
        END AS NewSpecialRank
    FROM counted ST
)
UPDATE numbered
SET SpecialRank = NewSpecialRank;

db<>fiddle

ValNik · Accepted Answer · 2025-07-11 16:23:24Z

As I understand your question, you need to exclude the first rows with the value 'No' from the ranking.
To do this, calculate the rank of the first row with the value 'Yes' and subtract it from FullRank.
(we will also add +1 so that the countdown starts from 1)

See example with some additional cases in source data

UPDATE #SpecialTemp
  set SpecialRank=t.newRank
FROM #SpecialTemp src
INNER JOIN (
  SELECT ID,FullRank
    ,FullRank-min( case when Special='Yes' then FullRank end)
                   over(partition by Id order by FullRank)+1 as newRank
  FROM #SpecialTemp
) t ON src.Id=t.Id and src.FullRank=t.FullRank

Result

ID	FullRank	Special	SpecialRank
1	1	No	null
1	2	Yes	1
1	3	No	2
2	1	Yes	1
2	2	No	2
3	1	No	null
3	2	Yes	1
3	3	Yes	2
3	4	No	3
3	5	No	4
4	1	No	null
4	2	No	null
4	3	Yes	1
4	4	No	2
4	5	Yes	3

fiddle

If we consider your approach, query may be like this

UPDATE #SpecialTemp 
   SET SpecialRank =case when STa.FullRank>=b.FullRank
                        then STa.FullRank-b.FullRank+1
                    end
FROM #SpecialTemp as STa
INNER JOIN (
   SELECT STb.ID ,MIN(STb.FullRank) FullRank
   FROM #SpecialTemp AS STb
   WHERE Special='Yes'
   GROUP BY STb.ID
) AS b
ON STa.ID = b.ID

Fiddle

Collectives™ on Stack Overflow

Apply a conditional rank to a temporary table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related