Add New Column with RowID

  • Hi, in the following example in this link:

    https://stackoverflow.com/questions/11116275/increment-row-number-on-group

    Suppose I want do add a new column s_index and order by shade.

    It would look like the following:

    Update MY_TABLE

    SET s_index = (Select ROW_NUMBER() OVER(PARTITION BY [shade] ORDER BY [shade]) )

    But the column just outputs values of 1's.

    Does anyone know what is wrong?

  • Does [Shade] contain unique values, or are there some duplicates? If there are duplicates, do you want all of the duplicates to have the same value?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Unique Values for one Shade: look at s_index.

    That's exactly what I want but I just need to create a new column in a table with that same output.

  • The problem with the example you reference is that there is no primary key on the table, prior to the generation of s_index. So how does any UPDATE query know which row to update, within the batch of duplicates?

    The usual way of performing such an update is along these lines (untested)

    WITH OrderedRows as (Select t1.PK, s_index = ROW_NUMBER() OVER(PARTITION BY t1.shade ORDER BY t1.shade) from tbl t1)

    UPDATE T2

    set s_index = OrderedRows.s_index

    from tbl t2

    join OrderedRows on t2.PK = OrderedRows.PK

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Well I need to write a query that would create a new column and append it to the table in the link and output as it would under s_index.

    Are you sure I need a primary key? Is there any other way?

  • Nusc (7/31/2016)


    Well I need to write a query that would create a new column and append it to the table in the link and output as it would under s_index.

    Are you sure I need a primary key? Is there any other way?

    It's possible, but first ...

    Can I reverse this question on you and ask why you are creating a table that allows duplicate data rows?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I need to plot year, name, lat, long coordinates for hurricane paths using SSRS.

    I need the same idea of what's done here:

    https://dba.stackexchange.com/questions/24954/convert-spatial-points-data-to-line-data-for-use-in-line-layer-in-sql-server-rep

    In particular:

    SELECT

    a.ptorder,

    a.lat,

    a.long,

    a.city,

    b.ptorder,

    b.lat,

    b.long,

    b.city AS nextcity,

    CAST('LINESTRING(' + CAST(a.long AS VARCHAR) +' '+ CAST(a.lat AS VARCHAR) +', '+

    CAST(b.long AS VARCHAR) +' '+ CAST(b.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata

    FROM

    pointdatatable a

    LEFT OUTER JOIN

    pointdatatable b

    ON

    b.ptorder = a.ptorder + 1

    WHERE

    b.ptorder IS NOT NULL

    ORDER BY a.ptorder

    But in this case ptorder would correspond to the rowid.

  • Nusc (7/31/2016)


    Hi, in the following example in this link:

    https://stackoverflow.com/questions/11116275/increment-row-number-on-group

    Suppose I want do add a new column s_index and order by shade.

    It would look like the following:

    Update MY_TABLE

    SET s_index = (Select ROW_NUMBER() OVER(PARTITION BY [shade] ORDER BY [shade]) )

    But the column just outputs values of 1's.

    Does anyone know what is wrong?

    WRT your original post on this thread...

    The Stack Overflow link you provided is spot on for producing the proper return. The problem occurs when you try to do an UPDATE to a table that need Rank/Windowing functions.

    Also, let's not mess around with only a small number of rows... let's test not only for accuracy but performance as well. With that in mind, here's a wee bit more test data.

    --=================================================================================================

    -- Create the test table. THIS IS NOT A PART OF THE SOLUTION!

    -- We're just building a test table to demo with here.

    -- This takes very little time even though we're creating a million row test table.

    --=================================================================================================

    --===== If the test table already exists, drop it to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

    DROP TABLE #TestTable

    ;

    --===== Create the test table

    CREATE TABLE #TestTable

    (

    t_index INT

    ,s_index INT

    ,shade CHAR(1)

    )

    ;

    --===== Create a more substantial test with all the letters of the alphabet.

    INSERT INTO #TestTable

    (shade)

    SELECT TOP 1000000

    shade = CHAR(ABS(CHECKSUM(NEWID())%26)+65) --The magic of basic arithmetic ;-)

    FROM sys.all_columns ac1 --Not taking anything from these... it's just a "presence of rows"

    CROSS JOIN sys.all_columns ac2 --acting as a "pseudo-cursor".

    ;

    Now that we have that, here's a solution that uses the magic of an "Updateable CTE" to solve the problem.

    --=================================================================================================

    -- Solve the problem using an "Updateable CTE" to overcome the problems with doing updates

    -- that are based on Ranking/Windowing Functions.

    -- The UPDATE takes only 4 seconds (it's updating 2 columns for the whole table and doing

    -- sorts in the process)

    --=================================================================================================

    --===== Update the "index" columns of the #TestTable using the magic of an updateable CTE

    WITH cteRank AS

    (

    SELECT tRank = DENSE_RANK() OVER (ORDER BY shade)

    ,sRank = ROW_NUMBER() OVER (PARTITION BY shade ORDER BY shade)

    ,t_index

    ,s_index

    FROM #TestTable

    )

    UPDATE cteRank

    SET t_index = tRank

    ,s_index = sRank

    ;

    --===== Let's see what we've got --This part takes about 6 seconds in the Grid Mode.

    SELECT *

    FROM #TestTable

    ORDER BY t_index, s_index

    ;

    Shifting gears a bit, I strongly agree with the others... Even if you have to use a surrogate key for this, there should be something in the table that uniquely identifies the rows. Consider adding a decent clustered index, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am open to that option but I need to do what's required in this link:

    https://dba.stackexchange.com/questions/24954/convert-spatial-points-data-to-line-data-for-use-in-line-layer-in-sql-server-rep

    THe issue with what Jeff wrote is that for my actual data,

    In one of the new "shades", the "s_index) starts off with 29 then increments. I don't understand why it doesn't start at 1.

    Even if I load that, it will load, then output the following errors:

    (88407 row(s) affected)

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online.

    Microsoft.SqlServer.Types.GLArgumentException:

    at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)

    at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)

    at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)

    at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)

    at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

    at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

    .

    I'm really lost now. I thought all I had to do was in that link but it appears to be more complicated.

    I have attached my cleaned data. It is originally a .csv but I cannot upload that format type on this forum.

    Look at Willy and Emily. They don't start at 1 and I don't know why.

    I have attached my code. Please give your thoughts.

    ALTER TABLE [dbo].[hurracane]

    ADD rowid int

    WITH cteRank AS

    (

    SELECT

    indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])

    ,RowID

    FROM [dbo].[hurracane]

    )

    UPDATE cteRank

    SET RowID = indexid

    ;

    SELECT

    a.rowid,

    a.Season,

    a.Name,

    a.Latitude,

    a.Longitude,

    b.rowid,

    b.Season,

    b.Name,

    b.Latitude,

    b.Longitude,

    CAST('LINESTRING(' + CAST( a.Longitude AS VARCHAR) +' '+ CAST(a.Latitude AS VARCHAR) +', '+

    CAST(b.Longitude AS VARCHAR) +' '+ CAST(b.Latitude AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata

    FROM

    hurracane a

    LEFT OUTER JOIN

    hurracane b

    ON

    b.rowid = a.rowid + 1

    WHERE

    b.rowid IS NOT NULL

    ORDER BY a.rowid

  • Nusc (7/31/2016)


    I am open to that option but I need to do what's required in this link:

    https://dba.stackexchange.com/questions/24954/convert-spatial-points-data-to-line-data-for-use-in-line-layer-in-sql-server-rep

    THe issue with what Jeff wrote is that for my actual data,

    In one of the new "shades", the "s_index) starts off with 29 then increments. I don't understand why it doesn't start at 1.

    Even if I load that, it will load, then output the following errors:

    (88407 row(s) affected)

    Msg 6522, Level 16, State 1, Line 1

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online.

    Microsoft.SqlServer.Types.GLArgumentException:

    at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)

    at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)

    at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)

    at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)

    at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

    at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

    .

    I'm really lost now. I thought all I had to do was in that link but it appears to be more complicated.

    I have attached my cleaned data. It is originally a .csv but I cannot upload that format type on this forum.

    Look at Willy and Emily. They don't start at 1 and I don't know why.

    I have attached my code. Please give your thoughts.

    ALTER TABLE [dbo].[hurracane]

    ADD rowid int

    WITH cteRank AS

    (

    SELECT

    indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])

    ,RowID

    FROM [dbo].[hurracane]

    )

    UPDATE cteRank

    SET RowID = indexid

    ;

    SELECT

    a.rowid,

    a.Season,

    a.Name,

    a.Latitude,

    a.Longitude,

    b.rowid,

    b.Season,

    b.Name,

    b.Latitude,

    b.Longitude,

    CAST('LINESTRING(' + CAST( a.Longitude AS VARCHAR) +' '+ CAST(a.Latitude AS VARCHAR) +', '+

    CAST(b.Longitude AS VARCHAR) +' '+ CAST(b.Latitude AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata

    FROM

    hurracane a

    LEFT OUTER JOIN

    hurracane b

    ON

    b.rowid = a.rowid + 1

    WHERE

    b.rowid IS NOT NULL

    ORDER BY a.rowid

    Please post the CREATE TABLE statement that goes along with your data. Please be sure to include any indexes and constraints.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Why? Just import the xlsx file. There's too many entries.

    I also reuploaded the file to include headers.

  • Nusc (7/31/2016)


    Why? Just import the xlsx file. There's too many entries.

    I also reuploaded the file to include headers.

    I'm planning on importing your data. I'd like to know what the actual table for the columns involved looks like so that me guessing doesn't lead to incorrect answers. You've already spent more time responding above than generating the code for the CREATE TABLE statement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • IF you don't mind saving that xlsx as a csv file and importing as a flat file source so there are no differences would be great.

    Year , name, latitude, longitude, rowid

    RIght now they're all assigned as varchar except for rowid which is assigned as an int.

    Maybe that's my problem?

  • here is your spread sheet data for "Emily" setup for others to use.

    you will see that "Emily" does have a rowid of 1`......

    CREATE TABLE hurracane(

    Year VARCHAR(4) NOT NULL

    ,Name VARCHAR(10) NOT NULL

    ,Latitude VARCHAR(10) NOT NULL

    ,Longitude VARCHAR(10) NOT NULL

    ,RowID INTEGER NOT NULL

    );

    INSERT INTO hurracane(Year,Name,Latitude,Longitude,RowID) VALUES

    ('1987','EMILY','9.8','-51.3',104),('1987','EMILY','10.4','-53',105),('1987','EMILY','10.9','-54.7',106),('1987','EMILY','11.4','-56.4',107)

    ,('1987','EMILY','12','-58',108),('1987','EMILY','12.4','-59.7',109),('1987','EMILY','13.1','-61.3',110),('1987','EMILY','13.7','-63.1',111)

    ,('1987','EMILY','14.5','-64.7',112),('1987','EMILY','15.1','-66.3',113),('1987','EMILY','15.9','-67.7',114),('1987','EMILY','16.7','-69.1',115)

    ,('1987','EMILY','17.8','-70.4',116),('1987','EMILY','18.3','-71',117),('1987','EMILY','19','-71.5',118),('1987','EMILY','20','-72.3',119)

    ,('1987','EMILY','20.9','-72.8',120),('1987','EMILY','22','-73',121),('1987','EMILY','23.2','-73',122),('1987','EMILY','24.4','-72.7',123)

    ,('1987','EMILY','26','-72',124),('1987','EMILY','28','-70.5',125),('1987','EMILY','30.2','-68',126),('1987','EMILY','32.3','-64.7',127)

    ,('1987','EMILY','32.4','-64.6',128),('1987','EMILY','35','-60',129),('1987','EMILY','38','-55',130),('1987','EMILY','41.2','-49',131)

    ,('1987','EMILY','44.8','-42.5',132),('1987','EMILY','49','-36',133),('1993','EMILY','19.9','-52.6',43),('1993','EMILY','20.5','-53.6',44)

    ,('1993','EMILY','21.3','-54.8',45),('1993','EMILY','22.3','-56',46),('1993','EMILY','23.2','-57.1',47),('1993','EMILY','24.3','-57.8',48)

    ,('1993','EMILY','25.4','-58.6',49),('1993','EMILY','26.7','-59.5',50),('1993','EMILY','27.6','-60',51),('1993','EMILY','28','-60.3',52)

    ,('1993','EMILY','27.9','-60.5',53),('1993','EMILY','28','-60.4',54),('1993','EMILY','28.2','-60.4',55),('1993','EMILY','28.3','-60.7',56)

    ,('1993','EMILY','27.9','-61',57),('1993','EMILY','27.4','-61.2',58),('1993','EMILY','26.9','-61.7',59),('1993','EMILY','26.6','-62.4',60)

    ,('1993','EMILY','26.4','-63',61),('1993','EMILY','26.3','-63.5',62),('1993','EMILY','26.4','-64.4',63),('1993','EMILY','26.6','-65.2',64)

    ,('1993','EMILY','27','-66.1',65),('1993','EMILY','27.4','-66.9',66),('1993','EMILY','28','-67.6',67),('1993','EMILY','28.6','-68.2',68)

    ,('1993','EMILY','29.3','-68.8',69),('1993','EMILY','30','-69.2',70),('1993','EMILY','30.6','-69.7',71),('1993','EMILY','31.2','-70.2',72)

    ,('1993','EMILY','31.5','-70.8',73),('1993','EMILY','31.8','-71.4',74),('1993','EMILY','32','-72.2',75),('1993','EMILY','32.4','-73',76)

    ,('1993','EMILY','32.9','-73.8',77),('1993','EMILY','33.6','-74.7',78),('1993','EMILY','34.5','-75.2',79),('1993','EMILY','35.2','-75.1',80)

    ,('1993','EMILY','35.6','-74.9',81),('1993','EMILY','36.6','-74.4',82),('1993','EMILY','37.5','-72.7',83),('1993','EMILY','38.2','-70.7',84)

    ,('1993','EMILY','39','-68.5',85),('1993','EMILY','39.2','-66',86),('1993','EMILY','39.2','-63.6',87),('1993','EMILY','39','-61.4',88)

    ,('1993','EMILY','38.6','-59.6',89),('1993','EMILY','38.1','-58.3',90),('1993','EMILY','37.5','-57.7',91),('1993','EMILY','36.9','-57.5',92)

    ,('1993','EMILY','36.4','-57.6',93),('1993','EMILY','36','-57.6',94),('1993','EMILY','35.8','-57.5',95),('1993','EMILY','36.1','-57.2',96)

    ,('1993','EMILY','36.7','-56.9',97),('1993','EMILY','37.4','-56.4',98),('1993','EMILY','38','-55.7',99),('1993','EMILY','38.7','-54.8',100)

    ,('1993','EMILY','39','-53',101),('1993','EMILY','39.3','-51.1',102),('1993','EMILY','39.8','-49.4',103),('1999','EMILY','11.5','-53.6',25)

    ,('1999','EMILY','11.5','-53.8',26),('1999','EMILY','11.6','-53.9',27),('1999','EMILY','12.1','-53.9',28),('1999','EMILY','12.6','-54.2',29)

    ,('1999','EMILY','12.8','-54.8',30),('1999','EMILY','13.2','-55.2',31),('1999','EMILY','13.8','-55.7',32),('1999','EMILY','14.3','-56.2',33)

    ,('1999','EMILY','15','-56.6',34),('1999','EMILY','15.8','-57',35),('1999','EMILY','17','-57.1',36),('1999','EMILY','18','-57',37)

    ,('1999','EMILY','19','-57',38),('1999','EMILY','20','-57',39),('1999','EMILY','21.1','-56.6',40),('1999','EMILY','22.4','-56.7',41)

    ,('1999','EMILY','23.8','-56.7',42),('2005','EMILY','10.7','-42.4',134),('2005','EMILY','10.8','-43.4',135),('2005','EMILY','10.9','-44.4',136)

    ,('2005','EMILY','11','-45.4',137),('2005','EMILY','11','-46.8',138),('2005','EMILY','11','-48.5',139),('2005','EMILY','11','-50.2',140)

    ,('2005','EMILY','11','-52',141),('2005','EMILY','11','-53.7',142),('2005','EMILY','11.1','-55.4',143),('2005','EMILY','11.2','-57.2',144)

    ,('2005','EMILY','11.4','-58.9',145),('2005','EMILY','11.6','-60.2',146),('2005','EMILY','11.9','-61.5',147),('2005','EMILY','12','-61.8',148)

    ,('2005','EMILY','12.4','-63.2',149),('2005','EMILY','12.9','-64.9',150),('2005','EMILY','13.3','-66.7',151),('2005','EMILY','13.7','-68.4',152)

    ,('2005','EMILY','14.1','-70.1',153),('2005','EMILY','14.5','-71.8',154),('2005','EMILY','14.9','-73.4',155),('2005','EMILY','15.4','-75',156)

    ,('2005','EMILY','15.9','-76.5',157),('2005','EMILY','16.4','-78',158),('2005','EMILY','17.1','-79.5',159),('2005','EMILY','17.7','-81.2',160)

    ,('2005','EMILY','18.3','-82.8',161),('2005','EMILY','18.9','-84.3',162),('2005','EMILY','19.5','-85.8',163),('2005','EMILY','20.3','-87.3',164)

    ,('2005','EMILY','20.3','-87.4',165),('2005','EMILY','21.3','-88.9',166),('2005','EMILY','22','-90.3',167),('2005','EMILY','22.6','-91.5',168)

    ,('2005','EMILY','23.2','-92.8',169),('2005','EMILY','23.7','-94',170),('2005','EMILY','24.1','-95.1',171),('2005','EMILY','24.4','-96.1',172)

    ,('2005','EMILY','24.6','-96.9',173),('2005','EMILY','24.8','-97.6',174),('2005','EMILY','25','-98.7',175),('2005','EMILY','25','-99.7',176)

    ,('2005','EMILY','25','-100.5',177),('2005','EMILY','25','-101.1',178),('2011','EMILY','14.9','-61.4',1),('2011','EMILY','15.1','-62.5',2)

    ,('2011','EMILY','15.4','-63.6',3),('2011','EMILY','15.7','-64.8',4),('2011','EMILY','16','-66.2',5),('2011','EMILY','16.3','-67.7',6)

    ,('2011','EMILY','16.6','-69.1',7),('2011','EMILY','16.8','-70.3',8),('2011','EMILY','16.9','-70.7',9),('2011','EMILY','16.9','-71.3',10)

    ,('2011','EMILY','17.3','-72.2',11),('2011','EMILY','17.9','-73.4',12),('2011','EMILY','18.8','-74.6',13),('2011','EMILY','19.8','-75.5',14)

    ,('2011','EMILY','21','-76.3',15),('2011','EMILY','22.4','-76.9',16),('2011','EMILY','23.6','-77.4',17),('2011','EMILY','24.6','-77.9',18)

    ,('2011','EMILY','25.6','-78.1',19),('2011','EMILY','26.4','-78.2',20),('2011','EMILY','27.2','-78.1',21),('2011','EMILY','28.4','-77.4',22)

    ,('2011','EMILY','29.7','-76.4',23),('2011','EMILY','30.7','-74.8',24);

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • J Livingston SQL (8/1/2016)


    here is your spread sheet data for "Emily" setup for others to use.

    you will see that "Emily" does have a rowid of 1`......

    No, not for year 2005 there is no rowid of 1. I don't understand why.

Viewing 15 posts - 1 through 15 (of 69 total)

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