SQL Pulling a Row for Next or Previous Row of a Current Row

sql pulling a row for next or previous row of a current row

This is what I use for finding previous/next records. Any column in your table can be used as the sort column, and no joins or nasty hacks are required:

Next record (date greater than current record):

SELECT id, title, MIN(created) AS created_date
FROM photo
WHERE created >
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

Previous record (date less than current record):

SELECT id, title, MAX(created) AS created_date
FROM photo
WHERE created <
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

Example:

CREATE TABLE `photo` (
    `id` VARCHAR(5) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `created` DATETIME NOT NULL,
    INDEX `created` (`created` ASC),
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('XEi43', 'my family',       '2009-08-04');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('dDls',  'friends group',   '2009-08-05');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('32kJ',  'beautiful place', '2009-08-06');
INSERT INTO `photo` (`id`, `title`, `created`) VALUES ('EOIk',  'working late',    '2009-08-07');

SELECT * FROM photo ORDER BY created;
+-------+-----------------+---------------------+
| id    | title           | created             |
+-------+-----------------+---------------------+
| XEi43 | my family       | 2009-08-04 00:00:00 |
| dDls  | friends group   | 2009-08-05 00:00:00 |
| 32kJ  | beautiful place | 2009-08-06 00:00:00 |
| EOIk  | working late    | 2009-08-07 00:00:00 |
+-------+-----------------+---------------------+

SELECT id, title, MIN(created) AS next_date
FROM photo
WHERE created >
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created ASC
LIMIT 1;

+------+--------------+---------------------+
| id   | title        | next_date           |
+------+--------------+---------------------+
| EOIk | working late | 2009-08-07 00:00:00 |
+------+--------------+---------------------+

SELECT id, title, MAX(created) AS prev_date
FROM photo
WHERE created <
  (SELECT created FROM photo WHERE id = '32kJ')
GROUP BY created
ORDER BY created DESC
LIMIT 1;

+------+---------------+---------------------+
| id   | title         | prev_date           |
+------+---------------+---------------------+
| dDls | friends group | 2009-08-05 00:00:00 |
+------+---------------+---------------------+

Query data from previous row and update current row

Why not just sort by the keys and date and then use the lag function to look one row back. Maybe you already tried this, but used the lag only inside the if type is missing block. This won't help you as noted in the documentation

Storing values at the bottom of the queue and returning values from
the top of the queue occurs only when the function is executed. An
occurrence of the LAG n function that is executed conditionally stores
and return values only from the observations for which the condition
is satisfied.

Instead, compute the lag in every row, not just in those satisfying the condition.

proc sort data=have;
  by ID1 ID2 day;
run;

data want;
  set have;
  by ID1 ID2;
  lag_amount_a = lag(amount_a);
  lag_amount_b = lag(amount_b);
  lag_day = lag(day);
  lag_type = lag(type);
  if ID1 in ("a1", "a2", "a3") and missing(type) then do;
    // check if row before matches ID1, ID2 and day - 1
    if not first.ID2 and day = lag_day + 1 then do;
      amount_a = lag_amount_a;
      amount_b = lag_amount_b;
      type = lag_type;
    end;
  end;
run;

How to check the current row with next row and update the current row not using while loop and cursor

Here it is in recursive cte

declare @tbl table
(
    id  int,
    [value] decimal(5,2)
)

insert into @tbl values
(1, 0.00),
(2, 0.30),
(3, 0.00),
(4, 0.15),
(5, 0.20),
(6, 0.10),
(7, 0.10),
(8, 0.00),
(9, 0.00);

declare @new_value decimal(5,2) = 0.3;

with rcte as
(
    select  id, value, 
            new_value = iif(value > (@new_value * 0.2) 
                        and value < @new_value,
                        value,
                        new_value)
    from    @tbl
    where   id  = 1

    union all

    select  t.id, t.value, 
            new_value = iif(t.value > (r.new_value * 0.2) 
                        and t.value < r.new_value,
                        t.value,
                        r.new_value)
    from    rcte r
            inner join @tbl t   on  r.id    = t.id - 1
)
select  *
from    rcte

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 compare the current row with next and previous row in PostgreSQL?

This is my solution using WINDOW functions. I used the lag and lead functions. Both returns a value from a column from a row in offset from the current row. lag goes back and lead goes next in the offset.

SELECT tokcat.text
FROM (
    SELECT text, category, chartype, lag(category,1) OVER w as previousCategory, lead(category,1) OVER w as nextCategory
    FROM token t, textBlockHasToken tb
    WHERE tb.tokenId = t.id
    WINDOW w AS (
        PARTITION BY textBlockId, sentence
        ORDER BY textBlockId, sentence, position
    )
) tokcat
WHERE 'NAME' = ANY(previousCategory)
AND 'NAME' = ANY(nextCategory)
AND 'NAME' <> ANY(category)

---

Simplified version:

SELECT text
FROM (
    SELECT text
          ,category 
          ,lag(category) OVER w as previous_cat
          ,lead(category) OVER w as next_cat
    FROM   token t
    JOIN   textblockhastoken tb ON tb.tokenid = t.id
    WINDOW w AS (PARTITION BY textblockid, sentence ORDER BY position)
    ) tokcat
WHERE  category <> 'NAME'
AND    previous_cat = 'NAME'
AND    next_cat = 'NAME';

Major points

• = ANY() is not needed, the window function returns a single value
• some redundant fields in the subquery
• no need to order by columns, that you PARTITION BY - the ORDER BY applies within partitions
• Don't use mixed case identifiers without quoting, it only leads to confusion. (Better yet: don't use mixed case identifiers in PostgreSQL ever)

---

Related Topics