SQL find next record by non-unique field

Question

I need to find the next record in a database with (say) a given Surname. So if the user is looking at A Smith, the query would return either the Next A Smith, or B Smith

the solution is probably similar to that discussed here : http://stackoverflow.com/questions/638065/selecting-subsequent-records-arbitrarily-with-limit but using an ODBC 2.0 compliant database rather than mysql.

At the moment I have a procedural solution, but it is very slow. Is there a more elegant approach which uses only basic SQL ?

Sample Data in "appropriate" order

recnum, surname, firstnames 
1000,smith,andrew 
1320,smith,andrew 
1340,smith,andrew 
14,smith,anton 
17,smith,anton 
2000,smith,brian 
2030,smith,brian 
1017,smith,brianally 

I would have the recnum of the current record and need to find the next in the above (which is ordered on surname, forenames, recnum) In this example the case for 1320 is easy, but 1340 makes it harder!

Answer 1

If by 'next' you mean alphabetical on first name, then try:

  Select * From Table T
  Where lastName = @GivenSurname
     And firstName = 
      (Select Min(FirstName)
       From Table 
       Where LastName = T.LastName
          And FirstName > @GivenFirstName)

If there might be more than one record with this same first name and last Name, and if you have a surrogate Primary Key, then you can arbitrarilly pick one of them by using this:

  Select * From Table T1
  Where lastName = @GivenSurname
     And PK = 
      (Select Min(PK) From Table T2
       Where LastName = T1.LastName
         And FirstName =
          (Select Min(FirstName)
           From Table
           Where LastName = T1.LastName
                And FirstName > @GivenFirstName))

Answer 2

SQL is intended to work with and return set of data. In fact, one of the points Coad (or one of the gurus of relationalism anyway) uses to describe a relational database is that there is no "next record" -- the concept shouldn't even exist.

As such, from the viewpoint of SQL, what you should do is create a set that contains the data you want in the order you want:

select * from YourTable
    where LastName = @whatever
    order by FirstName

Then, if you want to work with one record at a time, you probably want a cursor into that data set. Using that, you'll just look at a record, and when you advance the cursor to the next record, it'll be the one that should be next.

Answer 3

Given your sample data and assuming that at any point in the process you have the "recnum" value available for the current record, it would be possible to query for the next value by using a series of up to three queries. If you think of the three pieces of data (surname, firstname, recnum) as a composite index, then you want to find the record that comes next in index order. So assuming you have the three pieces of data from the current record, run this:

select top 1 surname, firstname, recnum from Table
    where surname = @CurrentLast and 
    firstname = @CurrentFirst and 
    Recnum > @CurrRec
    order by surname, firstname, recnum

If that is empty, then run this:

select top 1 surname, firstname, recnum from Table
    where surname = @CurrentLast and 
    firstname > @CurrentFirst
    order by surname, firstname, recnum

And finally, if that is empty:

select top 1 surname, firstname, recnum from Table
    where surname > @CurrentLast
    order by surname, firstname, recnum

If the three fields are indexed, it should be reasonably efficient. It would also be possible to use a single query such as the following (syntax probably wrong), but it would require a composite index on all three fields in order to be efficient.

select top 1 surname, firstname, recnum from Table
    where surname+firstname+convert(recnum,char) > @CombinedValue
    order by surname, firstname, recnum

If recnum refers to a physical record number in the underlying data and if the SQL engine uses the record number as the tie breaker when other conditions match in sorting, then I believe that recnum could be left out of the order by clauses.

Answer 4

Does "no state preservation" mean that you can't store any data at all, even pre-caching? In other words, query

SELECT recnum FROM my_table ORDER BY surname, firstname

Use getrows to store that in a local array. Then iterate through that array, each time executing the prepared statement

SELECT surname, firstname FROM my_table WHERE recnum = ?

That will almost certainly be slower than querying it all at once, but it will satisfy your goal.