SQL 服务器 : Query the next lowest value for a given parameter

Question

我有下表

GroupID Sequence    Name
1             10        Mary
1             25        Jack
1             17        Jill
2              3        Peter
2             42        Henry
2             50        Paul

以下查询返回具有给定组 (1) 和给定序列 17 的下一个最低序列 (10) 的行

SELECT TOP 1 *
FROM   dbo.customerassignmentgroup
WHERE  groupid = 1
       AND SEQUENCE < (SELECT MAX(SEQUENCE)
                       FROM   dbo.customerassignmentgroup i
                       WHERE  i.groupid = customerassignmentgroup .groupid)
       AND manualsequence < 17
ORDER  BY SEQUENCE DESC

还有其他方法吗？我尽量避免

WHERE i.groupid = customerassignmentgroup .groupid

在内部查询中，因为我需要将其转换为 SubSonic 中的查询

注意:我的数据库是SQL Server 2000

Answer 1

如果你像你一样使用 ORDER BY，你可以这样做:

SELECT TOP 1 *
FROM   dbo.customerassignmentgroup
WHERE  groupid = 1 AND SEQUENCE < 17
ORDER  BY SEQUENCE DESC

您可以通过以下方式避免使用排序:

SELECT *
FROM   dbo.customerassignmentgroup t1
WHERE  t1.groupid = 1
   AND t1.SEQUENCE = (SELECT MAX(t2.SEQUENCE)
                   FROM   dbo.customerassignmentgroup t2
                   WHERE  t2.groupid = 1 AND t2.SEQUENCE < 17)