Moby,
Ron did show you how. The problem is he demonstrated the set from which you
want to select your records; i.e.
Select ...
from....
where key < current_key
order by LNAME, FNAME, MI
Given a current key of
LNAME FNAME MI
JONES GRACE A
The first row returned would be:
Quote:>JONES GRACE F AHHEDS
To get just the first row, use the TOP qualifier; i.e.:
Select TOP 1 ...
from....
where key < current_key
order by LNAME, FNAME, MI
This will be fast if you have an index on LNAME, FNAME, MI.
With 7.0, it may hiccup on common names like SMITH and Jones, as FNAME may
be more selective. In the case where this happens too often, you might want
three indices: one each on LNAME, FNAME, MI. The access path will be a
index hash join, just make sure you do not have any "OR" qualifications in
your join or where clause (forces you back to nested iteration - can be
slow).
North
>Thanks for the quick response, but my original post was misleading.
>The column id is not an incremental counter, but ANYTHING from a Social
>Security number to 'ZZASSEA1123'.
>Your suggestion would show me the id that comes after the person named
>Grace E Jones, not the name that comes after Grace E Jones.
>What I'd like to be able to do is to quickly find the below sequence,
>ONE AT A TIME mind you, regardless of the id:
>LAST NAME FIRST NAME MI ID
>.
>.
>.
>JONES GRACE E 97655222
>JONES GRACE E 00000001
>JONES GRACE F AHHEDS
>JONES GRETTA Z 100LLP-AA
>JUPPO BOB 19980701
>.
>.
>.
>Thanks again for any additional help.
>> Moby,
>> You can store the index result in a variable and requery the table,
>finding
>> the minimum of what's left. Let's assume you're searching by id. For
>> example, you do your first SELECT to get the id
>'Grace' AND
>> mi = 'E'
>> -- To get the 'next' one,
>> SELECT MIN(id)
>> FROM test
>> etc.
>> Ron
>> Ron Talmage
>> SQL Server MVP, MCSD, MCP
>> --
>> DECLARE
>> >I feel like I've forgotten how to do something REAL easy - but I
>need
>> >help.
>> >How do I select the next/previous record from a table using 1-4
>fields
>> >as the reference 'start' point WITHOUT using a cursor? I've set it
>up
>> >using several nested ifs, but it is very slow. The reason, I think,
>is
>> >the ORDER BY clause needed to sort so I can get the 'Next' or
>> >'Previous' record.
>> >Here's an example of what I've got:
>> >create table test
>> >(
>> >id char(15) primary key,
>> >lastnm char(15),
>> >firstnm char(15),
>> >mi char(1)
>> >)
>> >CREATE INDEX ix_name ON test (lastnm, firstnm, mi, id)
>> >At this point I have 2 indexes. Obviously, I can do a search and
>> >quickly get any record. The problem is when I try to do an 'ORDER
>BY'
>> >to determine which record is 'Next', it appears to have to sort the
>> >entire result set, and not just show me the next record.
>> >HELP!! - Any help is appreciated.
>> >Sent via Deja.com http://www.deja.com/
>> >Share what you know. Learn what you don't.
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
by