SQL Server: Query the next lowest value for a given parameter

I have the following table

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

The following query returns a row with the next lowest sequence (10) for a given group (1) and a given sequence of 17

SELECT TOP 1 *
FROM   dbo.customerassignmentgroup
WHERE  groupid = 1
       AND SEQUENCE < (SELECT MAX(SEQUENCE)
                       FROM   dbo.customerassignmentgroup i
     开发者_Python百科                  WHERE  i.groupid = customerassignmentgroup .groupid)
       AND manualsequence < 17
ORDER  BY SEQUENCE DESC

Is there another way of doing this? I am trying to avoid

WHERE i.groupid = customerassignmentgroup .groupid

in the inner query because I need to convert this to a query in SubSonic

Note: My database is SQL Server 2000

If you use ORDER BY as you do, you could just do:

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

You could avoid using sorting by doing:

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)

There is another way to do this but I have no idea if this helps with SubSonic. Basically move the subquery into the FROM.

SELECT TOP 1 *
FROM   dbo.customerassignmentgroup i
       INNER JOIN (SELECT MAX(SEQUENCE) SEQUENCE, groupid
                       FROM   dbo.customerassignmentgroup
                       GROUP BY groupid) maxSeq
       ON  i.groupid = maxSeq.groupid
          and i.SEQUENCE < maxSeq.SEQUENCE 
          and i.groupid = maxSeq.groupid

WHERE  groupid = 1

       AND manualsequence < 17
ORDER  BY SEQUENCE DESC