Result of the order request by list of values

存储架构 2017-11-15

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.

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

您可能感兴趣的

How we’re maintaining SQL Server Database infos in... As part of our current migration from a legacy hosting provider to AWS, we’re moving a number of databases into AWS RDS (Amazon’s Paas-ish database pl...
Importing GeoJSON Earthquake Data Into SQL Server Importing GeoJSON Earthquake Data Into SQL Server A significant portion of Yellowstone National Park sits on top of ...
SQL Server(MySQL)中的联合主键(联合索引) 索引分析... 最近有人问到这个问题,之前也一直没有深究联合索引具体使用逻辑,查阅多篇文章,并经过测试,得出一些结论 测试环境:SQL Server 2008 R2 测试结果与MySql联合索引查询机制类似,可以认为MySQL是一样的原理 ================================...
SQL: SELECT TOP 100 PERCENT is a code smell for SQ... We help a lot of customers upgrade their systems. Part of that job involves looking for code smells before starting the upgrades. If you aren’t fa...
Remote BLOB Storage and FILESTREAM in SQL Server 2... I'm a SharePoint develop and I have just learned about remote blob feature in SQL Server 2008 that makes storing BLOBs more effecient. My English's ...