Renumbering key column in SELECT query

  • I'm having a little trouble trying to a simple thing..

    We have a surrogate key column that I don't want to show to users, instead, I want to simplify the keys returned by the query in just 1,2,3.. format.

    For instance, I have a query that returns theses values:

    ID
    9990
    9990
    9995
    9995
    9999

    I want the query to return the following:

    ROW         ID
    1                9990
    1                9990
    2                9995
    2                9995
    3                9999

    The queries I've tried, using ROW_NUMBER don't seem to want to partition/number these correctly.. Any ideas?
    This query does NOT give me the results I'm hoping for..

    WITH [a] AS (

    SELECT 9990 [col1] UNION ALL SELECT 9990 UNION ALL SELECT 9995 UNION ALL SELECT 9995 UNION ALL SELECT 9999

    )

    SELECT

    ROW_NUMBER() OVER (PARTITION BY [col1] ORDER BY [col1]) [row]

    , [col1]

    FROM [a]

  • Use DENSE_RANK instead of ROW_NUMBER.  From the data given, you do not want a partition.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I would expect Rank or Dense_Rank to work, but they don't :unsure:. This is not very ellegant, but does the work:

    ; WITH [a] AS
    (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    , RowAssigned AS
    (
    SELECT [col1],
    row = row_number() OVER (ORDER BY [col1])
    FROM [a]
    GROUP BY [col1]
    )
    SELECT a.[Col1],RowAssigned.[row]
    FROM [a]
    JOIN RowAssigned ON RowAssigned.[col1] = a.[Col1]
    ORDER BY [row]
    ;

    I hope that this can be solved without GROUP BY in the middle, maybe someone else knows?

  • This gives the exact same results.  Why doesn't DENSE_RANK work?

    ; WITH [a] AS
    (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    SELECT [col1],
    row = DENSE_RANK() OVER (ORDER BY [col1])
    FROM [a]

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Zidar - Thursday, March 23, 2017 12:19 PM

    I would expect Rank or Dense_Rank to work, but they don't :unsure:. This is not very ellegant, but does the work:

    ; WITH [a] AS
    (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    , RowAssigned AS
    (
    SELECT [col1],
    row = row_number() OVER (ORDER BY [col1])
    FROM [a]
    GROUP BY [col1]
    )
    SELECT a.[Col1],RowAssigned.[row]
    FROM [a]
    JOIN RowAssigned ON RowAssigned.[col1] = a.[Col1]
    ORDER BY [row]
    ;

    I hope that this can be solved without GROUP BY in the middle, maybe someone else knows?

    DENSE_RANK() seems to work perfectly

    WITH [a] AS (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    SELECT DR = DENSE_RANK() OVER (ORDER BY col1)
    , col1
    FROM [a]

  • This gives the exact same results. Why doesn't DENSE_RANK work?

    because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for all rows:

    ; WITH [a] AS
    (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    SELECT [col1],
    row = DENSE_RANK() OVER (PARTITION BY [col1] ORDER BY [col1])
    FROM [a]

    Thank you Drew for helping.🙂

  • Thanks so much for the quick response!  It's a difficult thing to Google, and I only use the ranking functions occasionally.

    This returned exactly what I wanted:

    WITH [a] AS (

    SELECT 9990 [col1] UNION ALL SELECT 9990 UNION ALL SELECT 9995 UNION ALL SELECT 9995 UNION ALL SELECT 9999

    )

    SELECT

    DENSE_RANK() OVER (ORDER BY [col1]) [row]

    , [col1]

    FROM [a]

    row col1
    1 9990
    1 9990
    2 9995
    2 9995
    3 9999

  • Zidar - Thursday, March 23, 2017 12:31 PM

    This gives the exact same results. Why doesn't DENSE_RANK work?

    because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for all rows:

    ; WITH [a] AS
    (
    SELECT 9990 [col1] UNION ALL
    SELECT 9990 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9995 UNION ALL
    SELECT 9999
    )
    SELECT [col1],
    row = DENSE_RANK() OVER (PARTITION BY [col1] ORDER BY [col1])
    FROM [a]

    Thank you Drew for helping.🙂

    I did mention that you didn't need the partition in my original response.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply