How to select rows if we have date within 1 day range in sql developer

If I have a table with column Name and Creation_Date that looks like

Name, Creation
Sam, 01/01/2020
Sam, 01,02,2020
Jack, 12/31/2019
Jack, 10/21/2018
Jack, 12/30/2019
Hank, 01/01/2019

What I want is if Name matches and creation date for that name is within +/- 1 day, I would like to get those rows. In this case, I want 4 rows of data as output. I can only filter on certain range but not within Name level.

EDIT: expected rows are :

Name, Creation
Sam, 01/01/2020
Sam, 01,02,2020
Jack, 12/31/2019
Jack, 10/21/2018

Solution

Self join might help.

SQL> with test (name, creation) as
  2    (select 'Sam' , date '2020-01-01' from dual union all
  3     select 'Sam' , date '2020-01-02' from dual union all
  4     select 'Jack', date '2019-12-31' from dual union all
  5     select 'Jack', date '2018-10-21' from dual union all
  6     select 'Jack', date '2019-12-30' from dual union all
  7     select 'Hank', date '2019-01-01' from dual
  8    )
  9  select a.name, a.creation
 10  from test a join test b on a.name = b.name
 11               and abs(a.creation - b.creation) = 1
 12  order by name, creation;

NAME CREATION
---- ----------
Jack 12/30/2019
Jack 12/31/2019
Sam  01/01/2020
Sam  01/02/2020

SQL>

(BTW, I think you put the wrong Jack as desired output; not the one in 2018, but 2019).