CODEWITHSUNDEEP
sqlsql-servert-sql
JC6T
JC6T

Reputation: 165

How to go to next or previous record

I have table named INV with records like the one below: 

ID   | PROD      | WH | BRAND
200  |  0100760  | 01 |  ABC
100  |  010-1750 | 02 |  DEF
300  |  010-1750 | 01 |  SAM
4    |  1555     | 01 |  SAM

I am trying to use SQL(stored procedure) to go to next record in the database table.

Currently my sql is written like this:

SELECT TOP 1 INV.*
FROM INV
WHERE INV.PROD > @prod
ORDER BY PROD ASC

Using the sql above, when @prod is 0100760, then it will go to the next record which has prod value of 010-1750 and wh value of 02

when I use the sql above but when @prod is 010-1750 it will go to the next record with prod value of 1555.

I want to change my sql statement into something like this(I am not sure) :

SELECT TOP 1 INV.*
FROM INV
WHERE

IF (INV.PROD = @prod) 
 INV.PROD = @prod 
 AND INV.WH <> @wh
ELSE 
 INV.PROD > @prod

ORDER BY PROD ASC

I think I need to use CASE inside my SQL query but I am not sure how. I cannot use ORDER BY ID.

Lets say I am at the record with ID 200. when I run the stored procedure, it should go to the record with ID 100. When I am record with ID 100, it should go to the record with ID 300

Upvotes: 1

Views: 681

Answers (3)

Rahul Neekhra
Rahul Neekhra

Reputation: 810

Here is the sample code which may work for you and you can use as per your need. Use columns NextRecordID and PreviousID to fetch next record in your recursion.

CREATE TABLE #Temp
(
    ID      INT,
    PROD    VARCHAR(100),
    WH      VARCHAR(10),
    BRAND   VARCHAR(100)
)

INSERT INTO #Temp
SELECT 200,'0100760','01','ABC' UNION
SELECT 100,'010-1750','02','DEF' UNION
SELECT 300,'010-1750','01','SAM' UNION
SELECT 4,'1555','01','SAM' 

SELECT * FROM #Temp

SELECT 
    CASE 
        WHEN 
            LEAD(ID) OVER (PARTITION BY 1 ORDER BY ID) IS NULL THEN -1 
        ELSE 
            LEAD(ID) OVER (PARTITION BY 1 ORDER BY ID) END AS NextRecordID,
     CASE 
        WHEN 
            LAG(ID) OVER (PARTITION BY 1 ORDER BY ID) IS NULL THEN -1 
        ELSE 
            LAG(ID) OVER (PARTITION BY 1 ORDER BY ID ) END AS PreviousID,
    *
FROM 
    #Temp
ORDER BY 
    ID

Here is the output

enter image description here

Upvotes: 0

Sahil Dhoked
Sahil Dhoked

Reputation: 372

You can find where the record exists in the table and then get the next record. The main point of ambiguity is that the list has partial ordering among its records and not total ordering. If we order the records according to the (prod, id) column(s), only then there will be a total ordering.

See if code below works. 

SELECT TOP 1 INV.*
FROM INV
WHERE INV.PROD > @prod 
OR (
    INV.PROD=@prod 
    AND INV.ID > (SELECT ID FROM INV WHERE PROD + @prod AND WH = @wh)
    )

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Take your original query. Add the @wh so you can distinguish cases, and add the new ID criterion to the order-by:

SELECT TOP 1 INV.*
FROM INV
WHERE INV.PROD > @prod or (INV.PROD=@prod and INV.WH > @wh)
ORDER BY PROD ASC,ID ASC

After looking throught the comments, I assume that each prod-wh combination is unique, therefore I consider them as a "key to be scanned". I changed the original INV.WH <> @wh of my answer to INV.WH > @wh, thanks to Sahil's observation. But be aware that this won't work if there are rows that have same BOTH prod and wh.

Upvotes: 1

Related Questions