How do I get my sql result to display the preceding and subsequent row when where is_selected=true ?

Result I am trying to get:

alphabet_letter  | is_selected
----------------- -------------
 C               | f   --row preceding boolean match1
 D               | t   **--boolean match1**
 E               | f   --row following boolean match1
 H               | f   --row preceding boolean match2
 I               | t   **--boolean match2**
 J               | f   --row following boolean match2

Here is my table and data:

create table letters (alphabet_letter text, is_selected boolean);
insert into letters values('A', false);
insert into letters values('B', false);
insert into letters values('C', false);
insert into letters values('D', true);
insert into letters values('E', false);
insert into letters values('F', false);
insert into letters values('G', false);
insert into letters values('H', false);
insert into letters values('I', true);
insert into letters values('J', false);

My table looks like this:

alphabet_letter  | is_selected
----------------- -------------
 A               | f
 B               | f
 C               | f
 D               | t
 E               | f
 F               | f
 G               | f
 H               | f
 I               | t
 J               | f

CodePudding user response：

Try the following:

WITH rns AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY alphabet_letter) rn
    FROM letters
  )
SELECT DISTINCT D.alphabet_letter, D.is_selected 
FROM
rns T 
JOIN rns D
  ON T.rn IN (D.rn, D.rn 1, D.rn-1)
WHERE T.is_selected 
ORDER BY d.alphabet_letter        

See a demo

Or you may use EXISTS as the following:

WITH rns AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY alphabet_letter) rn
    FROM letters
  )
SELECT D.alphabet_letter, D.is_selected
FROM rns D 
WHERE EXISTS(SELECT 1 FROM rns T WHERE T.is_selected AND T.rn IN (D.rn, D.rn 1, D.rn-1))
ORDER BY D.alphabet_letter

See a demo

CodePudding user response：

Maybe your can use Lag and lead method to achieve.

https://www.postgresqltutorial.com/postgresql-window-function/postgresql-lead-function/

https://www.postgresqltutorial.com/postgresql-window-function/postgresql-lag-function/

 with cte as 
(
    select alphabet_letter
            , is_selected
            , lag(alphabet_letter, 1) OVER (ORDER BY alphabet_letter) AS PrevValue
            , lead(alphabet_letter, 1) OVER (ORDER BY alphabet_letter) AS NextValue
        From letters    
)
select PrevValue alphabet_letter
    From cte
    where is_selected = true
union 
select alphabet_letter
    From cte
    where is_selected = true
union
select NextValue
    From cte
    where is_selected = true    
order by alphabet_letter