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?
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.
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 = r.number order by r.ordering