How to reordering a list of numbers?

Question

I have a query that generates the following output.

PART    TEST
----    ----
10      2
10      4
10      5
11      1
11      2
11      4
11      5
17      1
17      2
17      3
17      4
17      5
18      1
18      2
18      3
18      4
18      5

I am trying to renumber the TEST starting with 1.

PART    NEW_TEST    OLD_TEST
----    --------    --------
10      1           2
10      2           4
10      3           5
11      1           1
11      2           2
11      3           4
11      4           5
17      1           1
17      2           2
17      3           3
17      4           4
17      5           5

I have been playing around with both RANK() and ROW_NUMBER() functions, but not getting the results that I am looking for.

select DISTINCT
    mptc.PART,
    RANK() OVER(ORDER BY mptc.PART ASC) AS TEST,
    ROW_NUMBER() OVER(PARTITION BY mptc.PART ORDER BY tpc.TEST ASC),
    tpc.TEST
from ri.TESTS tpc
join ri.PARTS mptc
  on tpc.PART_SYSID = mptc.PART
ORDER BY mptc.PART ASC

Is there another function that would do this...one that I am not aware of?

Thanks jlimited

Answer 1

I did some more searching and found my answer...Using the DENSE_RANK() function.

SELECT 
    mptc.PART_SYSID,
    DENSE_RANK() OVER(PARTITION BY mptc.PART_SYSID ORDER BY tpc.TEST) AS TEST_COLLECTION_PLAN,
    ROW_NUMBER() OVER(PARTITION BY mptc.PART_SYSID,tpc.TEST ORDER BY tpc.SYSID) AS TEST_COLLECTION_ORDER
FROM ri.TESTS tpc
JOIN ri.PARTS mptc
  ON tpc.PART_TEST_SYSID = mptc.SYSID

Thanks jlimited