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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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)
Code

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
Code

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;
Code

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
Code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.