SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Sort Data according to Order of IN clause Like SQL Order By

This Transact-SQL tutorial show to sort data filtered with SQL IN clause in WHERE criteria using the order of In clause without an ORDER BY clause.

I have a Users table and execute below SELECT statement. Although I did not specify an ORDER BY clause, I want to get the list sorted according to the IN clause I provide in the WHERE filter criteria.

/*
CREATE TABLE Users (
 UserId smallint identity(1,1),
 Username nvarchar(100)
)
Insert Into Users Values ('Eralper')
Insert Into Users Values ('Phantom')
Insert Into Users Values ('DarthVader')
Insert Into Users Values ('SQLDeveloper')
Insert Into Users Values ('BIDeveloper')
Insert Into Users Values ('DatabaseAdmin')
*/

Select * From Users Where UserId IN (6,1,5,2,4,3)

So I actually want to see the returned dataset sorted according to the order as seen in the WHERE clause IN criteria. The user with UserId equals to 6 should be returned as the first row. Following it, the user with UserId 1 comes next, etc.

On the other hand, SQL engine returns all users filtered with the WHERE clause unsorted or the data order provided by the table itself. Since I will not be able to sort filtered rows using UserId or any other column of the Users table, I need an additional data or criteria that will be attached with SQL IN clause items identifying their rank within the IN clause list.

To create and use the additional data for sorting the SQL IN clause list items, I will use a SQL string split function.

There are many SQL split function codes on the web if you make a search using the SQL split function keywords. The best performance can be achieved using SQL Server CLR Split function which is the fastest. But for simplicity and easy to create and use, I'll share with you the below SQL split function source code.

CREATE FUNCTION Split (
 @delimited nvarchar(max)
) RETURNS @t TABLE (
 orderid int identity(1,1), -- Id column is used to sort SQL IN clause items
 value nvarchar(max)
)
AS
BEGIN
 declare @delimiter char(1)
 declare @xml xml

 set @delimiter = ','
 set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

 insert into @t
 select r.value('.','varchar(5)')
 from @xml.nodes('//root/r') as records(r)

 RETURN
END
GO

To sort data in a SELECT statement according to the list order in IN CLAUSE, I'll use the IDENTITY column of the SQL Split function. As developers will realize the user defined function uses a temp table and stores data fragments, or items of the SQL IN Clause list into this temp table. While inserting splitted item data, the Identity columns "orderid" increments by one for each new item in the list. At the end of the execution, above SQL string split function will provide a means for developers to sort data of In clause with the written order.

select orderid, value from dbo.split('100,1,60,40,50,45') s order by orderid;

SQL

Now we are ready to combine Split function and our initial requirement to order dataset filtered parallel to the items in the IN clause. This time userid list of IN clause is assigned to a nvarchar() string variable which is passed as an input parameter to table valued split function.

declare @list nvarchar(max)
set @list = '6,1,5,2,4,3'

Select UserId, Username
From Users u
Inner Join dbo.Split(@list) s on s.value = u.UserId
Order By s.orderid

And here is the output of the above SQL Select statement where Users table and the Split function table is joined using UserId and splitted values. Please pay attention that the list is order by an ORDER BY clause where orderid is a column of the SQL split string function which splits input IN clause list.

SQL







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems