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