How to Get Records Before and After Given One

;WITH numberedlogtable AS
(
SELECT Id,Message, 
ROW_NUMBER() OVER (ORDER BY ID) AS RN
 FROM logtable
)

SELECT Id,Message
FROM numberedlogtable
WHERE RN IN (SELECT RN+i
             FROM numberedlogtable
             CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
             WHERE Message='Error')

id	name	date	last_update
3	test2	2021-01-02	2021-01-06
4	test3	2021-01-03	2021-01-06
5	test4	2021-01-04	null

How do I get records before found record with condition?

first of all, you need to find all min ids for 'true' values for every object_id. See the "with" part.

Once you have them, all is left to do is join your test_table with that result using object_id and add a condition to get all the lines with id smaller or equals to those you've found in the "with" part

with min_ids as (
select min(id) min_id, object_id
  from test_table
 where is_good = 'true'
 group by object_id)

select * 
  from test_table tt
  join min_ids mi
    on mi.object_id = tt.object_id
 where tt.id <= mi.min_id

dbfiddle

How do I get N records before given one?

You need to create a row number column if your Ids do not increment without gaps. Then you can use a simple join to find the previous N. Your previous N could overlap... so you have to add distinct if you do not want duplicates.

declare @N as integer
set @N=2

;with cte_tbl (Id, Message, rownum) AS
(
select *, ROW_NUMBER() over (order by id) as rownum from test
)
select distinct Prev.Id, Prev.Message
from cte_tbl
join cte_tbl Prev
on Prev.rownum between cte_tbl.rownum-@N and cte_tbl.rownum-1
where cte_tbl.Message = 'Error'
    and Prev.Message <> 'Error'
order by Prev.Id

If the one of the previous @N records is an error, the 'error' record will NOT show up. This would have to be modified if you do want those to be included. Just simply remove the line and Prev.Message <> 'Error'.

How do I get 5 records before AND after a record with a specific ID?

Try:

  SELECT * 
    FROM scores 
   WHERE score >= n
ORDER BY score ASC
   LIMIT 6

 UNION

  SELECT * 
    FROM scores 
   WHERE score < n
ORDER BY score DESC
   LIMIT 5

The syntax may vary somewhat depending upon what Database server you are using.

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

Easiest way to get all records after a specific row SQL

I am assuming that there is some column with which to determine whether a record is before or after the record with postID 201. From scanning your query, I'd say you have a timestamp column by which you want to order (to ignore the incremental nature of post ID).

If that is the case, one can employ a self join on the table some_table (for simplicity) where the timestamp columns of both table instance are compared. But one set of colums, is reduced to the timestamp of the record with postID = 201.

In other words, our join condition is 'all records of the table which have a timestamp larger than the one of the record with postID 201' which is the condition OP specified.

The result set now only contains records whose timestamp is larger than the one of postID 201 which we limit to only contain 25 entries. To get the ones directly after postID 201, we order by timestamp again.

The query could look like this:

SELECT 
  larger.*
FROM 
  some_table smaller
JOIN 
  some_table larger
  ON 
    smaller.timestamp < larger.timestamp
  AND smaller.postID = 201
ORDER BY larger.timestamp ASC
LIMIT 25

---

Related Topics