Post reply

Create a rowID

  • May 19, 2008 at 7:27 am

    #187766

    I have a table that looks like

    PER_ID SCH COUNT

    729976751

    779609541

    1210849032

    1210849502

    1214316691

    12164710811

    13122010831

    I want to add another column. Where the Count is 1 I want to next column to just be 1. Where the count is 2 I want to create a unique ID,

    e.g.

    PER_ID SCH COUNT

    729976751 1

    779609541 1

    1210849032 1

    1210849502 2

    1214316691 1

    12164710811 1

    13122010831 1

    I have tried IDENT_SEED and Ident_INCR but all it brings back is NULL values. I have a feeling Im not going to be able to do this one 🙁

    Debbie

  • May 19, 2008 at 7:32 am

    #817171

    SQL 2005?

    You may have some luck with the ROW_NUMBER function. Try partition by PER_ID, order by Sch

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 19, 2008 at 7:53 am

    #817180

    Im being told ROW_NUMBER is not a recognised function name and Im hoping to stay clear of partitioning for this particular problem.

    Mainly because its a bit beyond me :w00t:

  • May 19, 2008 at 8:00 am

    #817184

    Hi Debbie Edwards,

    Try this one....

    DROP TABLE #test

    CREATE TABLE #test(PER_ID int,SCH int,COUNTS int)

    INSERT #test (PER_ID,SCH,COUNTS)

    SELECT 72997,675,1

    UNION ALL

    SELECT 77960,954,1

    UNION ALL

    SELECT 121084,903,2

    UNION ALL

    SELECT 121084,950,2

    UNION ALL

    SELECT 121431,669,1

    UNION ALL

    SELECT 121647,1081,1

    UNION ALL

    SELECT 131220,1083,1

    SELECT PER_ID,SCH,COUNTS,case COUNTS when 1 then 1 else Row_Number() OVER(PARTITION BY COUNTS ORDER BY COUNTS) end as rownumber FROM #test

    ---

  • May 19, 2008 at 8:05 am

    #817189

    SELECT cs.tblPUPIL, cs.tblSCHOOL, tmp.TOTAL

    INTO #test

    FROM dbo.vtblPupil_CatchSchool cs

    INNER JOIN #COUNT_OF_PUPILS tmp

    ON cs.tblPUPIL = tmp.tblPUPIL

    ORDER BY cs.tblPUPIL

    SELECT tblPUPIL,tblSCHOOL,Total,

    case Total when 1 then 1 else Row_Number()

    OVER(PARTITION BY Total ORDER BY Total)

    end as rownumber FROM #test

    Hmmm Still wont accept ROW_NUMBER()

    Msg 195, Level 15, State 10, Line 1

    'Row_Number' is not a recognized function name.

    Im definitely using 2005 so thats very strange .....

  • May 19, 2008 at 8:14 am

    #817193

    HI,

    Are you sure you are set to compatibility 90 and not 80?

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 19, 2008 at 8:20 am

    #817195

    Debbie Edwards (5/19/2008)

    Im being told ROW_NUMBER is not a recognised function name and Im hoping to stay clear of partitioning for this particular problem.

    Mainly because its a bit beyond me :w00t:

    Partitioning in this case refers to some of the ROW_NUMBER syntax, not the "OTHER" partitioning (database partitioning).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 19, 2008 at 8:48 am

    #817217

    Christopher Stobbs (5/19/2008)

    HI,

    Are you sure you are set to compatibility 90 and not 80?

    thanks

    Chris

    I dont know anything about compatibility settings.....??? :doze:

  • May 19, 2008 at 8:51 am

    #817225

    Debbie - right-click on the database, then Properties, OPTIONS. There's a compatibility setting - what is it set to (DON'T mess with it, this would be a non-trivial change)?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 19, 2008 at 8:53 am

    #817229

    Ah ha... Thanks for that. Yes it is set to 80!

  • May 19, 2008 at 9:02 am

    #817238

    So - ROW_NUMBER will not work. Like I mentioned earlier, changing the compatibility mode is something that has potential to break some of your code, so while it's probably a good thing in the long run, it's not something you want to just do without testing (it's also a one-way ticket as I recall, so you can't "downgrade").

    You're now looking at using a SQL2000-compatible solution for Ordinal ranking. Jeff M. has a good set of examples on the matter in this article:

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 20, 2008 at 12:00 am

    #817582

    Matt Miller (5/19/2008)

    (it's also a one-way ticket as I recall, so you can't "downgrade").

    Compat mode can be changed up and down, no issues. All that happens if you drop the compat mode down is that newer features stop working.

    It's the move to a higher version database engine that's a one-way move.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 11 (of 11 total)

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