Post reply

Getting the next or previous sequential id

  • August 24, 2005 at 7:32 pm

    #87332

    Hi there,

    I need a sql query to get the next or previous sequential id where status >= 1 or status <= 3 given a current id. current id = 145 note: the gaps between where status is = (1, 2, 3) could be tens, hundreds, or thousands of record. example below: table structure and data: id status
    10 -1
    11 -1
    12 1
    13 ... 144 status are all = -1
    145 1
    146 ... 250 status are all = -1
    251 1
    252 ... 300 status are all = -1

    1. For the next, the query should return 251
    2. For the previous, the query should return 12

    Your help and response is greatly appreciated.

    Thanks

  • August 24, 2005 at 9:51 pm

    #584850

    you could try doing something like this:

    
SELECT PreviousID = MAX(ID) FROM myTbl WHERE seqID  145 AND STATUS BETWEEN 1 AND 3







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 25, 2005 at 5:30 am

    #584912 

    SELECT Position = 'Previous', seqID = MAX(ID) FROM myTbl WHERE seqID  145 AND STATUS BETWEEN 1 AND 3
ORDER BY 1 DESC

    making a mental note to myself never to answer posts close to midnight...







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 25, 2005 at 11:43 am

    #585105

    Both your solutions works great and I like to thank you very much for responding soon and helping me.

  • August 25, 2005 at 12:08 pm

    #585116

    happy to help! the reason for my second post was to explicitly display which was 'previous' and which 'next'...







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply