Default sort my own order without sort by-CodePudding

Select id from MyTable where id in (28,5,7,35)

I want to leave the order as is in the in Clause, however SQL output is ordered by ID

I know how to achieve the result using a temp table. Is there a better way to do it?

CodePudding user response：

You can use a case expression in the order by clause:

select id
from MyTable
where id in (28,5,7,35)
order by case id
when 28 then 1
when 5 then 2
when 7 then 3
when 35 then 4
end;

CodePudding user response：

Just another option using a bit of JSON

Example

Declare @S varchar(50) = '28,5,7,35'

Select A.*
 From  YourTable A
 Join  OpenJSON( '[' string_escape(@S,'json') ']' ) B on A.ID=B.Value
 Order by B.[Key]

If it helps with the visualization, the JSON portion returns the following... key becomes your actual sequence.

key value   type
0   28      2
1   5       2
2   7       2
3   35      2

Note: the string_escape() is just a precaution and may not be necessary provided you supply a delimited string of integers.

If you have alphanumerics, this would format the JSON array.

Declare @S varchar(50) = 'Act28,5,Usr7,ZZZ35'
Select * from OpenJSON( '["' replace(string_escape(@S,'json'),',','","') '"]' )

CodePudding user response：

Not sure what you mean by I know how to achieve the result using a temp table as you haven't shown this, however you could insert your values into a temp table / table variable with an Identity in the order you require the results, then inner join. This is likely the most performant option for larger result sets.

Note that if you want a result set in a specific order, you must specify an order by clause.

declare @filter table(seq int identity(1,1), id int)
insert into @filter values (28),(7),(5),(35)

select * 
from TheTable t
join @filter f on f.id = t.id
order by f.seq;