SQL Query: Can I select the next indexed row ONLY (without selecting more)?

Question

I want to do an SQL query. The 'where' clause uses 'greater-than' a target value. I want to retrieve exactly one and only one row, namely, the very row exactly after my target value. The catch is, I want it to be efficient; I don't want the select to find hundreds of records first, then serve up the single row I want; I want it to find the row I want (reminder: it's already an indexed row). Can SQL do this, and do it efficiently like an ISAM read would do?

Answer 1

errr.. This is a little tricky to understand the question but here goes:

find the next row after the last "greater than x" row... okay let's go with this example table called Fruit:

pk | name | amount
1 apples 5
2 oranges 3
3 bananas 7
4 grapes 9
5 pears 2

so I am assuming you want to find the pk that is the next indexed auto-incrementing row after the amount is greater than say, 6. so you Would you be trying to get row #5?

This might work:

SELECT pk
FROM Fruit
WHERE pk =
((Select top 1 pk FROM Fruit where amount > 6 order by amount DESC) + 1)

Seems a strange thing to want but I may not be understanding the question...

Answer 2

Sql alone can not do this but you can write your request so that you get just one row. and if the result of your query has more than one row you have to set some restrictions or you can write after your query (limit, 1).
this will give you just one row.
to cite an example.

select Something from MyTable where (your condations) limit, 1;

Answer 3

suppose in table1 there's a field 'b'. & u wanna find the first b that's greater than x then:
SELECT First(Table1.b) AS FirstOfb
FROM Table1
where b > 'x'