Re: retreiving the "next" record using SQL

O,

     Try this one:

  SELECT *
    FROM tablename
   WHERE TO_CHAR(column1) || column2 >= 'presetvalue' ORDER BY TO_CHAR(column1) || column2

You can also change the ordering to whatever you need. I am assuming that you are providing a concatinated string for the presetvalue. If not, you will need to change the first preset value: TO_CHAR(presetvalue1) || 'presetvalue2'

Jay!!!

ORcastillo wrote:

> I'm new to SQL and have the following question:
> I have a table whose primary key is a combination of 2 columns and the
> data types are "number + alpha string". We need to do a "next" operation
> but I don't seem to find something explicit in SQL for such a thing.
> I tried doing
>
> select * from tablename
> where column1 >= presetvalue
> and
> column2 >= 'presetvalue'
> order by column1, column2
>
> Well, it fails because if the "next" record's col2
> may be alphabetically less than what the sql statement is asking for.
> Ex. find the next record after col1= 1, col2 = '00804008'
> the next record is col1= 2, col2 = '000'
> based on the above sql statement the real next record doesn't meet the
> criteria, so we get something else
> I'm sure this is a common ocurrence!
Received on Thu Oct 22 1998 - 11:59:27 CDT