Sorting Numbers in SQL Server Without A Sorting Function

Hi. Today I am sorting numbers in SQL Server. Last week my friends attended an interview for the MNC company. He has more than two years of experience.  If you have more than two years of experience then  it is not possible for the interviewer to ask the SQL Server question. Since I do I expect the interviewer to ask the question of how to sort numbers in SQL Server without any predefined function. If you have a time limit then I am not 100% sure but some confusion was created in our small mind.  I decided today to make a complete program and share it with others. Let's start with me. 
 
For example I will sort the numbers 12,5,8,64,548,987,6542,4,285,11,26.
 
SQL Server has no array list or array so how can we hold the values after sorting the numbers? SQL Server has temporary tables. Temporary automatically creates and drops the table after the execution. For more information hit this link about temporary tables:
 
 
First of all, create a temporary table. Suppose a problem occurs in SQL Server or during program execution. A Temporary table can't be deleted or dropped the proper way. When we want to create a table a second time a confirm error occurs as in the following:
 
There is already an object named '#temp' in the database. 
 
So this type of problem is avoided by checking first if the table exists like this: 
 
  1. IF  EXISTS (SELECT * FROM sys.tables  
  2. WHERE name = N'#temp' AND type = 'U'--check the #temp already exists in database or not   
  3. --Not:-  type U stand for user  
  4. begin  
  5. drop table #temp  
  6. end 
 
If the table already exists in the database then drop the table #temp.
 
My values are 12,5,8,64,548,987,6542,4,285,11,26. They need to be be split up before the sort. How can we split the numbers? Of course we can at the comma (,).  If I split the at the comma then I get the numbers like this: 12 5 8 64 548 and so on. One question then arises is, how to split the value? Don't worry, I have done that.
  1. select left('12,45,18,95',(CHARINDEX(',','12,45,18,95')-1)) -- if i run this query it should be return the value is 12 
After that everything is fine, we get the value from the #temp table.

  1. select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value 
 
The following is a complete Stored Procedure to sort the numbers.  
  1. ALTER proc [dbo].[Porc_sortnumber]  
  2. as  
  3. begin  
  4. DECLARE @value VARCHAR(MAX)='1,2,5,6,12,88,47,95,56,20'  
  5. declare @lenth int =1  
  6. IF  EXISTS (SELECT * FROM sys.tables  
  7. WHERE name = N'#temp' AND type = 'U'--check the #temp allready exists in database or not   
  8. --Not:-  type U stand for user  
  9. begin  
  10. drop table #temp  
  11. end  
  12. create table #temp (id int identity(1,1),value int)   
  13. while(@lenth!=0 )  
  14. begin  
  15. insert into #temp(value) values(left(@value,(CHARINDEX(',',@value)-1)))  
  16. set @value= right(@value,len(@value)-((CHARINDEX(',',@value))))  
  17. set @lenth=CHARINDEX(',',@value)  
  18.   
  19. end  
  20. insert into #temp(value) values(@value)  
  21. select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value  
  22. end 
  Output :
 
 
 
I hope this article is very helpful for everyone. If there is any problem or any query then please comment. 
 


Similar Articles