July 31, 2016 at 10:29 am
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?
July 31, 2016 at 10:37 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2016 at 10:46 am
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.
July 31, 2016 at 11:00 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2016 at 11:09 am
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?
July 31, 2016 at 11:43 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2016 at 4:26 pm
I need to plot year, name, lat, long coordinates for hurricane paths using SSRS.
I need the same idea of what's done here:
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.
July 31, 2016 at 4:27 pm
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
Change is inevitable... Change for the better is not.
July 31, 2016 at 4:52 pm
I am open to that option but I need to do what's required in this link:
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
July 31, 2016 at 5:57 pm
Nusc (7/31/2016)
I am open to that option but I need to do what's required in this link: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
Change is inevitable... Change for the better is not.
July 31, 2016 at 6:20 pm
Why? Just import the xlsx file. There's too many entries.
I also reuploaded the file to include headers.
July 31, 2016 at 7:07 pm
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
Change is inevitable... Change for the better is not.
July 31, 2016 at 10:00 pm
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?
August 1, 2016 at 3:27 am
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
August 1, 2016 at 9:31 am
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