http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>
2down votefavorite<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>
1
share [g+]share [fb]share [tw]
I wonder if the SQL geniuses amongst us could lend me a helping hand.
I have a column VersionNo in a table Versions that contains 'version number' values like
VersionNo
---------
1.2.3.1
1.10.3.1
1.4.7.2
etc.
I am looking to sort this, but unfortunately, when I do a standard order by, it is treated as a string, so the order comes out as
VersionNo
---------
1.10.3.1
1.2.3.1
1.4.7.2
Intead of the following, which is what I am after:
VersionNo
---------
1.2.3.1
1.4.7.2
1.10.3.1
So, what I need to do is to sort by the numbers in reverse order (e.g. in a.b.c.d, I need to sort by d,c,b,a to get the correct sort ourder).
But I am stuck as to how to achieve this in a GENERIC way. Sure, I can split the string up using the various sql functions (e.g. left, right, substring, len, charindex), but I can't guarantee that there will always be 4 parts to the version number. I may have a list like this:
VersionNo
---------
1.2.3.1
1.3
1.4.7.2
1.7.1
1.10.3.1
1.16.8.0.1
Can, does anyone have any suggestions? Your help would be much appreciated.
Answer:
If You are using SQL Server 2008
select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);
What is hierarchyid<http://msdn.microsoft.com/en-us/library/bb677290.aspx>
Edit:
Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here<http://www.sqlmag.com/article/sql-server/solutions-to-t-sql-sorting-challenge.aspx>.
The challenge<http://www.sqlmag.com/article/sql-server/t-sql-sorting-challenge.aspx>
Note:
In SQL Server 2008 things are drastically simpler. With slight adjustment of the dot separated list of values, you get the canonical string representation a HIERARCHYID value. Either replace all dots in the string with slashes and add a slash at the beginning and end of the string, or simply add a slash at the beginning and end of the string. Either way, convert the adjusted string to HIERARCHYID, and the values would naturally sort correctly based on the integer values in the string segments. Several people came with such a solution: Razvan Socol, Pawel Potasinski, Peter DeBetta and myself. Here are two examples based on this approach:
select val
from dbo.t1
order by cast('/' + replace(val, '.', '/') + '/' as hierarchyid);
select val
from dbo.t1
order by cast('/' + val + '/' as hierarchyid);
Monday, May 21, 2012
SQL: How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query : Good One
Friday, March 2, 2012
SQL SERVER - How To Handle Deadlock: Nice Example
SQL SERVER - How To Handle Deadlock
By Nitin Chilka<http://www.codeproject.com/script/Membership/View.aspx?mid=3897642> | 22 Sep 2009
SQL2005<http://www.codeproject.com/search.aspx?aidlst=30>SQL<http://www.codeproject.com/search.aspx?aidlst=93>Windows<http://www.codeproject.com/search.aspx?aidlst=94>SQL-Server<http://www.codeproject.com/search.aspx?aidlst=101>DBA<http://www.codeproject.com/search.aspx?aidlst=117>Dev<http://www.codeproject.com/search.aspx?aidlst=118>Intermediate<http://www.codeproject.com/search.aspx?aidlst=153>
An article on how to handle deadlock
Introduction
A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.
When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
This article will explain how to handle deadlocks in a user-friendly way.
The Deadlock
Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.
The Deadlock Situation
The below example shows the deadlock situation between the two transactions.
Transaction A
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
BEGIN TRANSACTION
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
Transaction B
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
BEGIN TRANSACTION
UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
If both the transactions are executed at the same time, then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms, transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
But what if you don't like the default behavior (aborting the transaction)? Can you change it? Yes, you can, by rewriting Transactions A and B as shown below.
Transaction A
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Transaction B
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END CATCH
Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock (Error_Number 1205), the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction) using the below statement:
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
GOTO RETRY -- Go to Label RETRY
This statement is used to transfer the control to the label named RETRY (which is at the beginning).
Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully. Have a look into the outputs of the transaction where the exception occurred.
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
(1 row(s) affected)
Rollback Transaction
(1 row(s) affected)
(1 row(s) affected)
Using RetryCounter
Now, I guess you understood how to handle deadlock without aborting the transaction. Let's move to the next interesting topic about deadlock. Imagine if there are more than two processes that read/update the Customer or Orders table at the same time. Below, I have modified both the transactions where I have shown how we can use RetryCounter to solve the problem.
Transaction A
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if
-- still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
Transaction B
[cid:image001.gif@01CCF86D.7391A3F0]Collapse | Copy Code<http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock>
DECLARE @RetryCounter INT
SET @RetryCounter = 1
RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY
UPDATE Orders SET ShippingId = 12 Where OrderId = 221
WAITFOR DELAY '00:00:05' -- Wait for 5 ms
UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Rollback Transaction'
ROLLBACK TRANSACTION
DECLARE @DoRetry bit; -- Whether to Retry transaction or not
DECLARE @ErrorMessage varchar(500)
SET @doRetry = 0;
SET @ErrorMessage = ERROR_MESSAGE()
IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
BEGIN
SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
END
IF @DoRetry = 1
BEGIN
SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
BEGIN
RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message
-- if still deadlock occurred after three retries
END
ELSE
BEGIN
WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
GOTO RETRY -- Go to Label RETRY
END
END
ELSE
BEGIN
RAISERROR(@ErrorMessage, 18, 1)
END
END CATCH
The RetryCounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock (Error_Number 1205). In this example, the transaction can try to execute up to three times if it fails due to a deadlock. This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. So the transaction can try three times to check whether the required lock is available.
History
* 20th September, 2009: Initial version
License
This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)<http://www.codeproject.com/info/cpol10.aspx>
About the Author
Nitin Chilka<http://www.codeproject.com/Members/Nitin-Chilka>
References:
http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock
Monday, February 20, 2012
JOB: "Star F.A.D. Technology Pvt. Ltd." Interviews.
From: Varadhi Software <varadhi.soft@gmail.com>
Date: Mon, Feb 20, 2012 at 11:58 AM
Subject: Fwd: Fw: "Star F.A.D. Technology Pvt. Ltd." Interviews.
To:
In our organization ("Star FAD Technology Pvt. Ltd.") we have 6 vacancies. Anybody who ever is interested (male or female) qualified with Intermediate and any degree pass or fail with any computer course is eligible(Any Multimedia course is more preferable).
This is regarding computer editing and mixing job. Age limit is 18years to 25years. If the candidate is selected, the we give training for 15days (In this training period 50% salary would be paid to the selected candidates). Monthly salary would be 25000/-. Monday to Friday are working days and timing is 10am to 6pm.
Firstly we give agreement for 1year when a person joins. If his or her performance is good we extend the period upto five years and also every year salary will be hike up to 20%.
So, Interested candidates can come on Sunday to Wednesday 10am to 7pm i.e. on Feb 19th, 20th, 21th, 22nd & 23rd which is the last date of the interview . You should bring your last completed educational qualified certificate and also certificates of any completed computer courses & 2 passport size photos with an appropriate resume (Your certificates should be only xerox copies).
[Note:' This is not placement service this is our company openings only' and also give this message to friends, collegues and to all ur students. In these selected candidates will be sent to US after one year if their performance is good in their work]
Thanking you Sir/Madam,
M.Rajkumar Chowdary,.(Chairman & Director)
"Star FAD Technology Pvt. Ltd."
Interview Place:
Star F.A.D. Technology Pvt. Ltd.
V.V. Complex, Above Syndicate Bank,
4th Floor, Near Kalanikethan,
Chaitanyapuri,
Dilsukhnagar,
Hyderabad.
Contact No. 9705266097
Friday, January 20, 2012
SQL : Row-By-Row Processing Without Cursor
Row-By-Row Processing Without Cursor
Here is the Logic.....
--Create a Table Variable and Variable Counter
DECLARE @InitialTable TABLE(RowNo INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Field1 int, Field2 Int)
DECLARE @IncrementalCounter INT 'This variable is declare for Loop
--All values are inserted into Your Table Variable ........
Insert into @InitialTable (field1, Feld2)
Select A1, A2 from Your_Table Where Your_Condition
----------------------------------------------------------
--Initialize the Counter
SET @IncrementalCounter = 1
--Condition of Checking the No of records to be traverse
while @IncrementalCounter <= ISNULL((SELECT COUNT(RowNo) FROM @InitialTable),0)
BEGIN
--Here we set the variable value to the @variable
SELECT @Variable = Field1 FROM @InitalTable where RowNo = @IncrementalCounter
SET @IncrementalCounter = @incrementalCounter +1
END
Reference:
http://www.sqlservercentral.com/Forums/Topic327280-319-1.aspx
Wednesday, November 30, 2011
JOB in Aricent : Walk-in interviews for Senior Engineer /Technical Leader (Testing) on December 3, 2011 - Gurgaon
From: Suchi Gupta
From: Aricent IT On Behalf Of HR_TA_iRefer
Sent: Wednesday, November 30, 2011 4:03 PM
Subject: Walk-in interviews for Senior Engineer /Technical Leader (Testing) on December 3, 2011 - Gurgaon
[cid:_2_071B09B8071B0450003D856B65257958]
Dear Friends,
We are in the process of hiring testing professionals for Senior Engineer –Testing/Technical Leader-Testing role. Please refer your friends and relatives whose profiles match with the following requirements to participate in the selection process.
Job Title: Senior Engineer-Testing/Technical Leader-Testing
Job Location: Gurgaon
Qualification: B.E / B.Tech / M.Tech / MCA
Experience: 3 - 7 years
Job Code
Designation
Job Location
Intranet Links
Internet Links
JOB931
Senior Engineer - Testing (Test Administrator)
Gurgaon
Read More<http://home.intra.aricent.com/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=932>
Read More<https://vpn.aricent.com/+CSCO+1h756767633A2F2F75627A722E766167656E2E6E6576707261672E70627A++/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=932>
JOB932
Technical Leader - Testing (Configuration Lead)
Gurgaon
Read More<http://home.intra.aricent.com/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=933>
Read More<https://vpn.aricent.com/+CSCO+1h756767633A2F2F75627A722E766167656E2E6E6576707261672E70627A++/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=933>
JOB933
Senior Engineer - Testing (System Tester)
Gurgaon
Read More<http://home.intra.aricent.com/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=934>
Read More<https://vpn.aricent.com/+CSCO+1h756767633A2F2F75627A722E766167656E2E6E6576707261672E70627A++/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=934>
JOB934
Technical Leader - Testing (Routing/Switching)
Gurgaon
Read More<http://home.intra.aricent.com/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=935>
Read More<https://vpn.aricent.com/+CSCO+1h756767633A2F2F75627A722E766167656E2E6E6576707261672E70627A++/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=935>
JOB935
Technical Leader - Testing (Integration Engineer)
Gurgaon
Read More<http://home.intra.aricent.com/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=936>
Read More<https://vpn.aricent.com/+CSCO+1h756767633A2F2F75627A722E766167656E2E6E6576707261672E70627A++/functions/hr/Lists/iRefer%20Jobs/DispForm.aspx?ID=936>
Candidates meeting the eligibility criteria can appear for the selection process as per the below mentioned schedule:
Location
Gurgaon
Date
December 3, 2011 (Saturday)
Time
9:30 AM – 2:00 PM
Venue
Aricent Group
Plot 17,
Sector 18, Electronic City,
Gurgaon
Contact Person
Kamini Kinra
Terms and Conditions:
• Freshers and 2011 graduates are not applicable
• Candidates will be expected to make their own arrangements for travel and accommodation
• To avail the referral bonus, candidates must mention the referrer's employee ID at the time of interview
• All terms and conditions of the Aricent iRefer program will apply
So hurry up & ask your referrals to avail this great opportunity!
Friday, June 24, 2011
THIS IS THE ONLY TIME WE WILL SEE AND LIVE THIS EVENT.......]
It is interesting to note the dates of Fri & Sat for this JULY..
|
Thursday, June 23, 2011
About Warren Buffet
From: ram.patro
This man lives by his philosophy and has been paid handsomely by doing so. I love the two rules he gives to the CEO's of his 63 companies (see #7)!
Some valuable advice...
"I always knew I was going to be rich.
I don't think I ever doubted it for a minute"
- Warren Buffett
PLEASE PASS THIS ON AS IT IS CLASSIC

