Get Previous and Next Row from Rows Selected with (Where) Conditions

Get previous and next row from rows selected with (WHERE) conditions

you didn't specify your DBMS, so the following is ANSI SQL:

select prev_word, word, next_word
from (
    select id, 
           lag(word) over (order by id) as prev_word,
           word,
           lead(word) over (order by id) as next_word
    from words
) as t
where word = 'name';

SQLFiddle: http://sqlfiddle.com/#!12/7639e/1

How to get the Previous & next row based on condition

Is this what your after? (Updated to reflect edit [OrderDate] to question)

Declare @OderDetail table
(
    Id int primary key,
    OrderId int,
    ItemId int,
    OrderDate DateTime2,
    Lookup varchar(15)
)

INSERT INTO @OderDetail 
VALUES  
(1, 10, 1, '2018-06-11', 'A'), 
(2, 10, 2, '2018-06-11', 'BE'), --this
(3, 2, 1, '2018-06-04', 'DR'),
(4, 2, 2, '2018-06-04', 'D'),  --this
(5, 3, 2, '2018-06-14', 'DD'), --this
(6, 4, 2, '2018-06-14', 'R');

declare @ItemId  int=2 , @orderid int = 10;

Query

With cte As
(
Select ROW_NUMBER() OVER(ORDER BY OrderDate) AS RecN,
* 
From @OderDetail Where ItemId=@ItemId
) 
Select Id, OrderId, ItemId, [Lookup] From cte Where 
RecN Between ((Select Top 1 RecN From cte Where OrderId = @orderid) -1) And
((Select Top 1 RecN From cte Where  OrderId = @orderid) +1) 
Order by id

Result:

Id  OrderId ItemId  Lookup
2   10      2       BE
4   2       2       D
5   3       2       DD

How to get previous and next row's value effeciently in SQL server

Try this:

Alter PROCEDURE GetNextAndPreviousInsturmentID
(
    @InstrumentID   varchar(14),
    @PreviousInstrumentID   varchar(14) OUT,
    @NextInstrumentID   varchar(14) OUT
)
AS
BEGIN

    Declare @Ids TABLE(Id varchar(14))

    ;With normal As
    (   
        --Numerate our rows
        Select ROW_NUMBER() Over (Order by Cast(Documents.InstrumentID as decimal(18)) as RowNumber,
               Documents.InstrumentID
        From Documents 

    )
    --Insert three rows from our table with our id and previos/next id
    INSERT INTO @Ids(Id)
    SELECT TOP(3) normal.InstrumentID
    FROM normal
    WHERE RowNumber >=
        (
            SELECT RowNumber - 1
            FROM normal
            WHERE normal.InstrumentID = @InstrumentID
        )
    ORDER BY normal.RowNumber
    --select next and previos ids

    SELECT @PreviousInstrumentID = Min(CAST(Id as decimal(18))),
           @NextInstrumentID = MAX(CAST(Id as decimal(18)))
    FROM @Ids
END
GO

In MS SQL 2012 we have new window functions like FIRST_VALUE and LAST_VALUE, unfortunately in sql 2008 these functions are missing.

Conditional Select and Update rows with previous/next rows

Use a subquery that returns either the next row if it contains he same value of t or the previous row:

update tablename
set (col2, col3, ..., coln) = ( 
  select tab.col2, tab.col3, ..., tab.coln
  from tablename tab
  where (tab.id = tablename.id + 1 and tab.t = tablename.t) or (tab.id = tablename.id - 1)
  order by tab.id desc limit 1
)  
where (col2, col3, ..., coln) = (0, 0, ..., 0)
  and exists (
        select 1 from tablename tab 
        where  (tab.id = tablename.id + 1 and tab.t = tablename.t) or (tab.id = tablename.id - 1)
      )

See the demo.

How to fetch the previous and next rows based on a selected row?

Try it like this

DECLARE @tbl TABLE(ID INT,Name VARCHAR(100));
INSERT INTO @tbl VALUES
 (1160,'Kate')
,(1161,'Kelly')
,(1162,'Jenny')
,(1163,'Heather')
,(1164,'Ashley');

DECLARE @id INT=1162;

The query will look for the smallest bigger and the biggest smaller ID to the given parameter

SELECT * 
FROM @tbl AS t
WHERE ID = (SELECT MIN(x.ID) FROM @tbl AS x WHERE x.ID>@id)
   OR ID = (SELECT MAX(x.ID) FROM @tbl AS x WHERE x.ID<@id)

Fetching next row based on data of current row

Sorting using rowid is not good, you'd better set another sort column

select aa into res from (
    select t1.first_name || t1.last_name aa
      from 
    (
    select * 
      from tab t1 
     where 'GET' = P_MODE
     order by t1.rowid
    ) t1
     where rownum = 1
     
    union all

select t1.res
  from (
    select lead(t1.first_name || t1.last_name) over(order by t1.rowid) res,
         t1.*
      from tab1 t1
     where 'NEXT' = P_MODE
) t1
 where t1.first_name || t1.last_name = P_STR
) t1

If you use a function, you can put this SQL into the function, or you can query it directly.

Get before and after rows of selected rows with specific condition

I create index on my words column and set this code to get result quickly:

WITH CTE AS 
(SELECT * FROM WordsTable WHERE word=N'Name')
SELECT          
    t2.word AS previousWord,
    t1.word,
    t3.word AS nextWord
FROM
    WordsTable AS t2,
    CTE AS t1,
    WordsTable AS t3
WHERE
    (t2.ID + 1)= t1.ID AND
    (t3.ID - 1) = t1.ID

Select previous and next row with condition in MSSQL

With this table

create table prueba(
    id int ,
    BegCha int,
    EndCha int,
    Val int);

this data

id  BegCha  EndCha  Val
10  20      30      250
10  30      40      140
10  50      60      189
20  10      20      250
20  20      30      157
20  30      40      199
20  40      50      70

and this Query

WITH pruebaNumerada AS  
(  
    SELECT 
      ROW_NUMBER() OVER(ORDER BY id ASC) AS RowNumber,
      id, BegCha, EndCha, val
    FROM prueba 
) 
SELECT b.id, b.BegCha, b.EndCha, b.val
FROM pruebaNumerada a  
inner  join pruebaNumerada b on b.RowNumber  between a.RowNumber-1 and a.RowNumber+1
WHERE a.val >=250;  

I obtain this result

id  BegCha  EndCha  val
10  20      30      250
10  30      40      140
10  50      60      189
20  10      20      250
20  20      30      157

Is there a way to access the previous row value in a SELECT statement?

SQL has no built in notion of order, so you need to order by some column for this to be meaningful. Something like this:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

If you know how to order things but not how to get the previous value given the current one (EG, you want to order alphabetically) then I don't know of a way to do that in standard SQL, but most SQL implementations will have extensions to do it.

Here is a way for SQL server that works if you can order rows such that each one is distinct:

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

If you need to break ties, you can add as many columns as necessary to the ORDER BY.

---

Related Topics