Finding the very next record

SELECT UID, Surname, GivenNames
FROM Client
ORDER BY Surname, GivenNames, UID

returns

UID Surname GivenNames
=====================================
05DBE8A4-A4A3-4DE0 Miller Katie
536EE790-7BAF-4140 Miller Steve
48185E22-1C82-47E5 Miller Susan
91E3AD26-03CF-4950 Milton Brandon
85DBA6EB-53AB-4427 Milton John

(The real UID's are longer than that. I have shortened them to simplify this example.)

Now, suppose I have already retrieved one of these records, as the result of another query.

I know the UID of that record, and I know the Surname on that record, and I know the GivenNames on that record, and I want to be able to retrieve the very next record in the sequence above.

For the purpose of this example, the record I have retrieved is:

UID Surname GivenNames
=====================================
536EE790-7BAF-4140 Miller Steve

One way to go about this, would be:

SELECT TOP 1 UID, Surname, GivenNames
FROM Client
WHERE Surname>'Miller'
OR (Surname='Miller' AND GivenNames>'Steve')
OR (Surname='Miller'
AND GivenNames='Steve'
AND UID>'536EE790-7BAF-4140')
ORDER BY Surname, GivenNames, UID

The ORDER BY clause of this query ensures that records are retrieved in the sequence I want.  The WHERE clause excludes the record that I already have, and all the records that come before it. So, the record it returns will be:

UID                Surname GivenNames
=====================================
48185E22-1C82-47E5 Miller  Susan

If I want to retrieve more fields, which are not a part of the ordering, then they can be added to the SELECT clause without altering the rest of the query:

SELECT TOP 1 UID, Surname, GivenNames,
HairColor, ShoeSize, ...

If there are a lot of fields in the ordering, there is another way of writing this query, so that the WHERE clause is less complicated.

The query at the beginning of this article could also have been written as:

SELECT UID, Surname, GivenNames
FROM Client
ORDER BY Surname+GivenNames+CAST(UID AS char(36))

I can also retrieve the next record after Steve Miller, using the following query.

SELECT TOP 1 UID, Surname, GivenNames
FROM Client
WHERE Surname+GivenNames+CAST(UID AS char(36))>'MillerSteve536EE790-7BAF-4140'
ORDER BY Surname+GivenNames+CAST(UID AS char(36))

CAST is a function defined by the SQL-92 standard, that can be used to convert data from one data type to another (with certain limitations).

If you are wondering why anyone would want to iterate through a table one record at a time, then consider this: The above query only requires a small change (SELECT TOP n ...) to iterate through the table 2 records at a time, 10 records at a time, or however many records the user asks to see.

Adelle.