Add A List Of Numbers In A Column
If you didnt want to use itertools for some reason you could achieve this, this way. I have assumed what you want to do by the example result you have given:- ColumnOfNumbers = 10,12,13 def ListAddition(ColumnOfNumbers): #check if the list is longer than one item if len(ColumnOfNumbers). This might work (Using Python 2.7): x = 10,12,13 for i in range(len(x)): for j in range(i+1, len(x)): print xi,' + ', xj, ' = ', xi+xj Output: 10 + 12 = 22 10 + 13 = 23 12 + 13 = 25 Updated: Assuming the file has a line: 10, 12, 13 import csv f = open('test.dat', 'r') try: reader = csv.reader(f) for row in reader: # convert array of string to int # # For Python 3 change the below line as explained in above link # i.e.
Results = list(map(int, results)) results = map(int, row) for i in range(len(results)): for j in range(i+1, len(results)): print (resultsi+resultsj) finally: f.close. I have a file with list of numbers in a column.
The three numbers i mentioned as a test case, practically there are 11 numbers. I want to read those numbers in python and then want to do the operation as you mentioned. I can put all 11 numbers in x =10, 12, 13, 18, 19. but I felt it's better to read it from a file and then do the operation. If i put 11 numbers like x =10, 12, 13, 18, 19. I am getting the correct output but if i read the same numbers from a file and use the same coe then it's giving some nasty results – Jan 18 '17 at 16:53.
By: Last Updated: 2018-02-01 Related Tips: Problem I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column, I also need to populate the existing records with an incremental counter what options are there to do this? Solution The first approach that may come to mind is to add an identity column to your table if the table does not already have an identity column. We will take a look at this approach as well as looking at how to do this with a simple UPDATE statement.
Using an Identity Column to Increment the Value by 1 In this example we are going to create a table (to mimic a table that already exists), load 100,000 records and then alter the table to add the identity column with an increment of 1. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 17 ms. Table 'accounts'.
Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6281 ms, elapsed time = 48701 ms. SQL Server Execution Times: CPU time = 6281 ms, elapsed time = 48474 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. Using Variables To Update and Increment the Value by 1 In this example we create a similar table (to mimic a table that already exists), load it with 100,000 records, alter the table to add an INT column and then do the update.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 247 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. Table 'accounts2'.
Scan count 1, logical reads 26384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 4781 ms, elapsed time = 4856 ms. (100000 row(s) affected) SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
If we compare the statistics time and statistics i/o from the the update with the identity column to this approach the overall number of logical reads is just about the same, but the overall duration is about 10 times faster doing the update versus having to maintain the identity value. Using Variables To Update and Increment the Value by 10 Let's say we want to increment by 10 instead of by 1. We can do the update as we did above, but use a value of 10 to have the ids in increments of 10 for each record. For clarity purposes, I am going to first make the id column NULL for all records and then do the update. Warning: Possible Duplicate Values Two of our readers Tillman Dickson and Steve Ash have noted that they have run into an issue where duplicate values are created if this processs is run in parallel. Tillman noted this issue on a table that had over 11 million rows and Steve mentioned this issue on a very large table as well. I tried to duplicate the issue, but on the systems I tested with I was not able to recreate the issue.
This doesn't mean that on your systems you won't possibly face the same issue, so to avoid having duplicate values Tillman and Steve have suggested these approaches. Update rows using a CTE - Ervin Steckl;WITH a AS( SELECT ROWNUMBER OVER(ORDER BY (SELECT NULL)) as rn, id FROM accounts2 ) UPDATE a SET id=rn OPTION (MAXDOP 1) Summary Once you have created an identity column there is no easy way to renumber all of your values for each row. With the update approach you could do this over and over again by just rerunning the query and changing the values.
Next Steps. If you have the need to add a new sequential value to your tables or have the need to update an existing value in a sequential manner don't forget about this approach. Look for other Last Updated: 2018-02-01. Post a comment or let the author know this tip helped. All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published.
Required fields are marked with an asterisk (.).Name.Email Email me updates. NOTE. If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting. Signup for our newsletter I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors.
I have read the and understand I may unsubscribe at any time. Monday, July 30, 2018 - 3:09:07 PM - Jinnybat Nice, I would like to add one more query comparative to CTE. UPDATE REF SET REF.ID=REF.ROWID FROM( SELECT ID, ROWNUMBER OVER(ORDER BY (SELECT NULL)) AS ROWID )REF Regards Tuesday, March 13, 2018 - 4:42:44 AM - Mike Grimes Hi Greg, Thanks for the info, I had never seen that syntax before for setting an incremental id set @id = id = @id+1 Glad that it worked.
Regards, Mike. Thursday, March 08, 2018 - 4:06:38 AM - herman hi, External systems are injecting data in our database and the import table required an unique reference id. So we are using new data and above solutions are not serving our need.
Our database is oracle and we use the sequence functionality just calling the function increments the sequential number and this allowed up to respect the unique reference and avoid any duplicates or restarting of numbers. This is not identical to the function of identify as the starting number is irrelevant. ALTER TABLE accounts ADD id INT IDENTITY(1,1) GO I suppose that a simular functionality exists and this would be an other soluction. Also Is there no functionality that when you create a table, you can automatically allocate an unique sequential number as default value? Herman Saturday, February 27, 2016 - 4:41:21 PM - Oluyemi Thanks, Greg.
The article on Rank Function you referred me to is most helpful. Remain blessed. Friday, February 26, 2016 - 9:37:11 AM - Greg Robidoux Hi Oluyemi, check out this tip and look at the RANK function.
This should be able to help you do what you are looking at doing. Thanks -Greg Friday, February 26, 2016 - 9:34:38 AM - Greg Robidoux Thanks Ervin for another approach to doing this.
I will update the tip and add your approach as well.Greg Friday, February 26, 2016 - 4:30:14 AM - Oluyemi Thanks for the tutorial. I tried to implement the input of Tillman Dickson in a scenario in which the serial no should resent on change of group. I was not successful; rather it numbered the entire tblStudents serially. Please help me to indicate what I did wrong to enable me do it right. My Code is shown below. The students are to be grouped and numbered according to their group. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION DECLARE @id Smallint set @id = 0 WHILE @@FETCHSTATUS =0 BEGIN update tblStudents set @id = stno = @id+1 where bytLGACode = bytLGACode and bytschtype = bytschtype and intSchCode = intSchCode END COMMIT TRANSACTION Thursday, February 25, 2016 - 5:24:10 PM - Ervin Steckl Thanks for the tips.
Add A List Of Numbers In A Columna
The fact about possible parallelism issue is a good to know one. Yet another method using a windowing function, which uses less reads (scanning the table only once):;WITH a AS( SELECT ROWNUMBER OVER(ORDER BY (SELECT NULL)) as rn, id FROM accounts2 ) UPDATE a SET id=rn OPTION (MAXDOP 1) Statistics: Table 'accounts2'. Scan count 1, logical reads 13404, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (100000 row(s) affected) Note the 'ORDER BY (SELECT NULL)' clause which does the trick actually. Should you use 'ORDER BY fname', for example, you would get around 126k reads. Monday, February 22, 2016 - 9:19:41 AM - Greg Robidoux Hi Steve and Tillman, Thank you for providing input on this tip. I will make updates to the tip based on the issues you both encountered.
Thanks again, Greg Saturday, February 20, 2016 - 3:46:15 PM - Steve Ash Just to echo Tillman's comment about parallelism - i ran into this as well and unfortunately didn't catch it and wasted a day of work:/ You might want to update the post above since this shows up high in the list from Google. The simplest fix is just to use MAXDOP 1 which just disables parallism: declare @id bigint = 1 update SIMOASISSUSPICHISTRUN9 set @id = id = @id + 1 option (MAXDOP 1) Monday, February 15, 2016 - 10:23:04 AM - Greg Robidoux Hi Tillman, thanks for taking the time to peform all of these tests. I didn't do a test on such a large table, so your results are very interesting. Thanks again for showing a solution that worked for a very large table.Greg Sunday, February 14, 2016 - 6:16:47 PM - Tillman Dickson This is a sweet trick, but one with a nasty gotcha, SQL Server is multithreaded and when it can it threads queries aggressively. @ID isn't interlocked My use, gotcha and fix will quickly illustrate. I received an unindexed table with 11 000 000 rows of which several columns were NVARCHAR(MAX) of mixed English and Arabic. I performed a SELECT INTO with a SELECT 0 as ID, field field.
Excel Add A Column A Numbers Counting
I used your trick to generate unique values in the ID column (40 seconds for 11 million rows!!!!!!). I inserted these rows into an empty table of the same schema, but with ID actually having a clustered index. Insert failed due to key violations. For every unique ID value there were 8 rows which shared. Welcome to reentrancy. Reset ID column value to 0, wrapped your trick in a transaction, applied it.
No improvement. Reset ID columns to 0. Made the transaction serializable et voila! I now had unique values in the ID column and it run time was 2 seconds longer, nominal.