January 23, 2017 at 11:02 am
I have the following data
Customer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)
If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
January 23, 2017 at 11:10 am
ajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is it always going to be "1" that you want to delete if there is a "2" and "2" that you want to keep?
If so, you could do :
DELETE <schema>.<table>
WHERE RowNum = 1
AND CUSTOMER IN ( SELECT CUSTOMER FROM <schema>.<table> WHERE RowNum = 2);
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 23, 2017 at 11:27 am
bmg002 - Monday, January 23, 2017 11:10 AMajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is it always going to be "1" that you want to delete if there is a "2" and "2" that you want to keep?
If so, you could do :
DELETE <schema>.<table>
WHERE RowNum = 1
AND CUSTOMER IN ( SELECT CUSTOMER FROM <schema>.<table> WHERE RowNum = 2);
That works! Thank you so much!!
January 23, 2017 at 11:43 am
ajkarora - Monday, January 23, 2017 11:27 AMbmg002 - Monday, January 23, 2017 11:10 AMajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is it always going to be "1" that you want to delete if there is a "2" and "2" that you want to keep?
If so, you could do :
DELETE <schema>.<table>
WHERE RowNum = 1
AND CUSTOMER IN ( SELECT CUSTOMER FROM <schema>.<table> WHERE RowNum = 2);That works! Thank you so much!!
There are problems with the script like if you had a customer with RowNum 1, 2 and 3, you would have that customer with RowNum 2 and 3 still in the table.
2 good tricks that I highly recommend before running any code you find on the internet:
1- run it in test/dev first. never ever run code you find online on live unless you are 110% sure it'll do what you want
2- run it inside of a transaction with a select before and a select after and rollback the transaction.
With tip 2, the reason I suggest that is that if somebody online gives you a delete command (like I did), you could see what the change would do before going live with it. So the script would be:
BEGIN TRANSACTION test1
SELECT *
FROM <schema>.<table>
DELETE <schema>.<table>
WHERE RowNum = 1
AND CUSTOMER IN ( SELECT CUSTOMER FROM <schema>.<table> WHERE RowNum = 2);
SELECT *
FROM <schema>.<table>
ROLLBACK TRANSACTION test1
And if the changes look like what you are expecting, replace "ROLLBACK TRANSACTION test1" with "COMMIT TRANSACTION test1".
you can run the above code as often as you like and play with all of the numbers and no data will actually be changed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 23, 2017 at 11:14 pm
ajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is there a chance a customer has records with RowNum as 1, 2, 3, 4, ... ( more than 2 ) ?
What would you like to do in that case?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 24, 2017 at 12:45 pm
Kingston Dhasian - Monday, January 23, 2017 11:14 PMajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is there a chance a customer has records with RowNum as 1, 2, 3, 4, ... ( more than 2 ) ?
What would you like to do in that case?
I'm not the OP, but I think it would depend on the structure of the table and the columns, but I bet you could do a view with something like:SELECT DISTINCT Customer,
MAX(RowNum) OVER (PARTITION BY Customer),
MAX(SalesOrder) OVER (PARTITION BY Customer),
customer
FROM <schema>.<table>
And then you wouldn't need to worry about deleting anything as it would just hide things you didn't want to see. Where I work, I am very hesitant to remove any data.
But if you wanted to remove anything other than the largest value, there are a lot of different ways to do it. Cursors, temporary tables, CTE (I am not very familiar with CTE's, but I believe this is a case where they would work)... could even use a while loop if you enjoy having the process slow and painful.
Are you in that situation? If so, one way you could do it (presuming you have no identity columns) would be to use the above SELECT, drop that into a table variable (or temp table... depends on how many ROWs you have and how much memory you have), truncate the source table and then put all the data back in from the table variable. Mind you, if you have a lot of data you want to preserve OR a lot of indexes, those inserts will be painful.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 24, 2017 at 10:09 pm
bmg002 - Tuesday, January 24, 2017 12:45 PMKingston Dhasian - Monday, January 23, 2017 11:14 PMajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is there a chance a customer has records with RowNum as 1, 2, 3, 4, ... ( more than 2 ) ?
What would you like to do in that case?I'm not the OP, but I think it would depend on the structure of the table and the columns, but I bet you could do a view with something like:
SELECT DISTINCT Customer,
MAX(RowNum) OVER (PARTITION BY Customer),
MAX(SalesOrder) OVER (PARTITION BY Customer),
customer
FROM <schema>.<table>And then you wouldn't need to worry about deleting anything as it would just hide things you didn't want to see. Where I work, I am very hesitant to remove any data.
But if you wanted to remove anything other than the largest value, there are a lot of different ways to do it. Cursors, temporary tables, CTE (I am not very familiar with CTE's, but I believe this is a case where they would work)... could even use a while loop if you enjoy having the process slow and painful.Are you in that situation? If so, one way you could do it (presuming you have no identity columns) would be to use the above SELECT, drop that into a table variable (or temp table... depends on how many ROWs you have and how much memory you have), truncate the source table and then put all the data back in from the table variable. Mind you, if you have a lot of data you want to preserve OR a lot of indexes, those inserts will be painful.
This is how you would do it using a CTE. This is probably the best way to do it.; WITH cte_TableName AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY Customer ORDER BY RowNum DESC ) AS RN, *
FROM TableName
)
DELETE
FROM cte_TableName
WHERE RN > 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 25, 2017 at 7:11 am
Kingston Dhasian - Tuesday, January 24, 2017 10:09 PMbmg002 - Tuesday, January 24, 2017 12:45 PMKingston Dhasian - Monday, January 23, 2017 11:14 PMajkarora - Monday, January 23, 2017 11:02 AMI have the following dataCustomer RowNum SalesOrder Customer
(100915) (2) (100915-2) (ARORA)
(100915) (1) (100915-1) (ARORA)
(100576) (1) (100576-1 ) (AMKOR)
(100555) (1) ( 100555-1 ) (JACK)If you look at the first two records you'll see its the same customer. I need to keep the record with RowNum 2 and delete the record with RowNum 1. Is there any way to do a this in SQL? I'm using SQL Server 2008
Is there a chance a customer has records with RowNum as 1, 2, 3, 4, ... ( more than 2 ) ?
What would you like to do in that case?I'm not the OP, but I think it would depend on the structure of the table and the columns, but I bet you could do a view with something like:
SELECT DISTINCT Customer,
MAX(RowNum) OVER (PARTITION BY Customer),
MAX(SalesOrder) OVER (PARTITION BY Customer),
customer
FROM <schema>.<table>And then you wouldn't need to worry about deleting anything as it would just hide things you didn't want to see. Where I work, I am very hesitant to remove any data.
But if you wanted to remove anything other than the largest value, there are a lot of different ways to do it. Cursors, temporary tables, CTE (I am not very familiar with CTE's, but I believe this is a case where they would work)... could even use a while loop if you enjoy having the process slow and painful.Are you in that situation? If so, one way you could do it (presuming you have no identity columns) would be to use the above SELECT, drop that into a table variable (or temp table... depends on how many ROWs you have and how much memory you have), truncate the source table and then put all the data back in from the table variable. Mind you, if you have a lot of data you want to preserve OR a lot of indexes, those inserts will be painful.
This is how you would do it using a CTE. This is probably the best way to do it.
; WITH cte_TableName AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY Customer ORDER BY RowNum DESC ) AS RN, *
FROM TableName
)DELETE
FROM cte_TableName
WHERE RN > 1
Right. That makes sense.
I need to learn more about CTE.
Only thing with what you wrote is that it doesn't work in SQL Server 2008, does it? I am pretty sure the "ORDER BY" is not allowed in the windowing functions in 2008 and older.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2017 at 7:15 am
bmg002 - Wednesday, January 25, 2017 7:11 AMRight. That makes sense.
I need to learn more about CTE.
Only thing with what you wrote is that it doesn't work in SQL Server 2008, does it? I am pretty sure the "ORDER BY" is not allowed in the windowing functions in 2008 and older.
Window functions in 2008 definitely accept ORDER BY clauses, otherwise how would SQL now what order to put the row numbers in?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 25, 2017 at 7:25 am
Thom A - Wednesday, January 25, 2017 7:15 AMbmg002 - Wednesday, January 25, 2017 7:11 AMRight. That makes sense.
I need to learn more about CTE.
Only thing with what you wrote is that it doesn't work in SQL Server 2008, does it? I am pretty sure the "ORDER BY" is not allowed in the windowing functions in 2008 and older.Window functions in 2008 definitely accept ORDER BY clauses, otherwise how would SQL now what order to put the row numbers in?
Doh. it was me using an incorrect query. You are correct.
I was doing:
SELECT MAX(value) OVER (PARTITION BY value2 ORDER BY value3) AS values
FROM <schema>.<table>
and was getting:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
but if I replace "MAX(value)" with "ROW_NUMBER()" then it returns the expected values. Which brings up an off-topic question about why the order by is not allowed when using MAX?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2017 at 7:34 am
bmg002 - Wednesday, January 25, 2017 7:25 AMDoh. it was me using an incorrect query. You are correct.
I was doing:
SELECT MAX(value) OVER (PARTITION BY value2 ORDER BY value3) AS values
FROM <schema>.<table>
and was getting:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.but if I replace "MAX(value)" with "ROW_NUMBER()" then it returns the expected values. Which brings up an off-topic question about why the order by is not allowed when using MAX?
I don't have a 2008 instance to spin up, however, using an order by in a 2005 compatibility database I have works, so surprised yours didn't.
Either way, the ORDER BY in a MAX statement is irrelevant. Take the following list: 9,5,6,7,8,1,3,4,2. If I put that in order (1,2,3,4,5,6,7,8,9) the maximum value is still 9. Ordering doesn't change that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 25, 2017 at 7:42 am
Thom A - Wednesday, January 25, 2017 7:34 AMbmg002 - Wednesday, January 25, 2017 7:25 AMDoh. it was me using an incorrect query. You are correct.
I was doing:
SELECT MAX(value) OVER (PARTITION BY value2 ORDER BY value3) AS values
FROM <schema>.<table>
and was getting:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.but if I replace "MAX(value)" with "ROW_NUMBER()" then it returns the expected values. Which brings up an off-topic question about why the order by is not allowed when using MAX?
I don't have a 2008 instance to spin up, however, using an order by in a 2005 compatibility database I have works, so surprised yours didn't.
Either way, the ORDER BY in a MAX statement is irrelevant. Take the following list: 9,5,6,7,8,1,3,4,2. If I put that in order (1,2,3,4,5,6,7,8,9) the maximum value is still 9. Ordering doesn't change that.
Makes sense. Maybe that is why SQL complains? it is trying to be helpful by telling me "are you sure you know what you are doing?".
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2017 at 8:49 am
Thom A - Wednesday, January 25, 2017 7:34 AMbmg002 - Wednesday, January 25, 2017 7:25 AMDoh. it was me using an incorrect query. You are correct.
I was doing:
SELECT MAX(value) OVER (PARTITION BY value2 ORDER BY value3) AS values
FROM <schema>.<table>
and was getting:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.but if I replace "MAX(value)" with "ROW_NUMBER()" then it returns the expected values. Which brings up an off-topic question about why the order by is not allowed when using MAX?
I don't have a 2008 instance to spin up, however, using an order by in a 2005 compatibility database I have works, so surprised yours didn't.
Either way, the ORDER BY in a MAX statement is irrelevant. Take the following list: 9,5,6,7,8,1,3,4,2. If I put that in order (1,2,3,4,5,6,7,8,9) the maximum value is still 9. Ordering doesn't change that.
Actually, that's not true. ORDER BY was added to the MAX() windowed function in SQL 2012. When MAX() is used with an ORDER BY clause, it requires a ROWS/RANGE specification. If one is not supplied it uses the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The MAX value is definitely affected by having different window frames.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 25, 2017 at 8:58 am
drew.allen - Wednesday, January 25, 2017 8:49 AMThom A - Wednesday, January 25, 2017 7:34 AMbmg002 - Wednesday, January 25, 2017 7:25 AMDoh. it was me using an incorrect query. You are correct.
I was doing:
SELECT MAX(value) OVER (PARTITION BY value2 ORDER BY value3) AS values
FROM <schema>.<table>
and was getting:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.but if I replace "MAX(value)" with "ROW_NUMBER()" then it returns the expected values. Which brings up an off-topic question about why the order by is not allowed when using MAX?
I don't have a 2008 instance to spin up, however, using an order by in a 2005 compatibility database I have works, so surprised yours didn't.
Either way, the ORDER BY in a MAX statement is irrelevant. Take the following list: 9,5,6,7,8,1,3,4,2. If I put that in order (1,2,3,4,5,6,7,8,9) the maximum value is still 9. Ordering doesn't change that.
Actually, that's not true. ORDER BY was added to the MAX() windowed function in SQL 2012. When MAX() is used with an ORDER BY clause, it requires a ROWS/RANGE specification. If one is not supplied it uses the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The MAX value is definitely affected by having different window frames.
Drew
I'm really showing some ignorance here aren't I?
I do understand that MAX would be affected by windowing functions (like if you wanted the max serial number for a specific product category). And I can see how using the range would make that work nicely too. Although a good example slips my mind at the moment. But if you were using a range without an order table, you could get inconsistent results so I do get that. It makes sense.
I'm starting to think I should stop replying to the post as every time I do, I find out my response was not correct LOL. Good way to learn mind you.
I did know about the range stuff and that works really nicely for running totals like if you want to keep track of the number of widgets sold and your table contains widget name, date and number sold, you could do a:
SELECT WidgetName, DateSold, NumSold, SUM(NumSold) OVER (PARTITION BY WidgetName ORDER BY DateSold RANGE UNBOUND PRECEDING AND CURRENT ROW) AS RunningTotal
FROM <table>
which could be useful for sales guys or for reporting and sales predictions. BUT that requires 2012 or higher (I believe... watch me be wrong on this too LOL).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 25, 2017 at 9:08 am
drew.allen - Wednesday, January 25, 2017 8:49 AMActually, that's not true. ORDER BY was added to the MAX() windowed function in SQL 2012. When MAX() is used with an ORDER BY clause, it requires a ROWS/RANGE specification. If one is not supplied it uses the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The MAX value is definitely affected by having different window frames.Drew
Thanks Drew, not something i was aware of.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply