Post reply

Increment column from 1-10 in select statement, tally or simple ROW_NUMBER?

  • February 7, 2012 at 10:33 am

    #252504

    Hello all,

    What's the best way to increment a column in a select statement? I know that this will ALWAYS be 10 rows, and I want to number them 1-10:

    USE tempdb

    CREATE TABLE #tableA (columnA INT IDENTITY(1000,1), columnB VARCHAR(3), columnC VARCHAR(3))

    INSERT INTO #tableA

    SELECT 'aaa','abc'

    UNION ALL

    SELECT 'bbb','def'

    UNION ALL

    SELECT 'ccc','ghi'

    UNION ALL

    SELECT 'ddd','jkl'

    UNION ALL

    SELECT 'eee','mno'

    UNION ALL

    SELECT 'fff','pqr'

    UNION ALL

    SELECT 'ggg','stu'

    UNION ALL

    SELECT 'hhh','vwx'

    UNION ALL

    SELECT 'iii','yza'

    UNION ALL

    SELECT 'jjj','bcd'

    UNION ALL

    SELECT 'zzz','efg'

    SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY columnA DESC) rn, columnB, columnC

    FROM #tableA

    ORDER BY columnA DESC

    So this gives me my 1-10, however I want the 10 to be with zzz and 1 to be with bbb... Basically I want that column to be in descending order while the data is in the descending order as well. If I take the DESC out of the OVER clause, I will not get 1-10, but 2-11. Any thoughts? I'm certainly not very familiar with ROW_NUMBER or the OVER clauses.

    Jared
    CE - Microsoft

  • February 7, 2012 at 10:45 am

    #1443966

    It seems too simple, so this may not be what you want:

    SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY columnA ASC) rn, columnB, columnC

    FROM #tableA

    There are 11 rows in your sample data. Are you aware of that?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • February 7, 2012 at 10:46 am

    #1443968

    I am confused by this. You say you know this will always be 10 rows, yet you have 11 rows in your sample data. Am I missing something?

  • February 7, 2012 at 10:47 am

    #1443972

    Is this what you're after?

    SELECT ROW_NUMBER() OVER(ORDER BY columnA desc)

    rn, columnB, columnC

    FROM (SELECT TOP 10 * FROM #tableA ORDER BY columnA DESC) a

    The key is that you need to get your top 10 first before using ROW_NUMBER() - TOP just limits the returned results and won't affect how ROW_NUMBER does it's counts

  • February 7, 2012 at 10:49 am

    #1443974

    HowardW (2/7/2012)

    Is this what you're after?

    SELECT ROW_NUMBER() OVER(ORDER BY columnA desc)

    rn, columnB, columnC

    FROM (SELECT TOP 10 * FROM #tableA ORDER BY columnA DESC) a

    The key is that you need to get your top 10 first before using ROW_NUMBER() - TOP just limits the returned results and won't affect how ROW_NUMBER does it's counts

    I think this will work! @rory, the results will always be 10. Not the table.

    Jared
    CE - Microsoft

  • February 7, 2012 at 10:51 am

    #1443975

    Ah, sorry about that. That and Howard's solution make sense now.

Viewing 6 posts - 1 through 5 (of 5 total)

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