Ordering query result by list of values

Question

I'm working on a sql query that is passed a list of values as a parameter, like

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)

This list is constructed of relations used througout the database, and must be kept in this order.

I would like to order the results by this list. I only need the first result, but it could be the one with GroupId 7 in this case.

I can't query like

order by (24,12,7,14,65).indexOf(GroupId)

Does anyone know how to do this?

Additional info:
Building a join works and running it in the mssql query editor, but...

Due to limitiations of the software sending the query to mssql, I have to pass it to some internal query builder as 1 parameter, thus "24,12,7,14,65". And I don't know upfront how many numbers there will be in this list, could be 2, could be 20.

Answer 1

Do a join with a temporary table, in which you have the values that you want to filter by as rows. Add a column to it that has the order that you want as the second column, and sort by it.

Answer 2

Use a table variable or temporary table with an identity column, feed in your values and join to that, e.g.

declare @rank table (
    ordering int identity(1,1)
    , number int 
    )

insert into @rank values (24)
insert into @rank values (12)
insert into @rank values (7)
insert into @rank values (14)
insert into @rank values (65)

select  pg.*
from    ProductGroups pg
left outer join 
    @rank r
on  pg.GroupId = t.number 
order by 
    r.ordering

Answer 3

You can also order by on a CASE:

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)
order by case GroupId 
    when 7 then 1 -- First in ordering
    when 14 then 2 -- Second
    else 3
end

Answer 4

I think I might have found a possible solution (but it's ugly):

select * 
from ProductGroups
where GroupID in (24,12,7,14,65)
order by charindex(
             ','+cast(GroupID as varchar)+',' ,
             ','+'24,12,7,14,65'+',')

this will order the rows by the position they occur in the list. And I can pass the string like I need too.