Oracle SQL: Return record with earliest and latest date for each ID and product name

Question

I have a table looking like this:

ID  Product DATE
1   A       1
1   B       1
1   A       2
1   B       2
1   A       3
1   B       3
2   A       1
2   B       1
2   A       2
2   B       2
2   A       3
2   B       3
.   .
.   .

I want for every ID and every product to see the rows only for the first and the last date. So the output would look like this:

ID  Product DATE
1   A       1
1   B       1
1   A       3
1   B       3

2   A       1
2   B       1
2   A       3
2   B       3

With this I have manage to get the first date only:

select *
from (
    select t.*, rank() over(partition by t.ID order by t.DATE) rn
    from t
) t
where rn = 1

Is this the correct way? And how can I add the last date as well?

Answer 1

I would use window functions:

select t.*
from (select t.*,
             min(date) over (partition by id) as min_date,
             max(date) over (partition by id) as max_date
      from t
     ) t
where date in (min_date, max_date);

An alternative method is to use correlated subqueries:

select t.*
from t
where t.date = (select min(t.date) 
                from t t2 
                where t2.id = t.id
               ) or
      t.date = (select max(t.date) 
                from t t2 
                where t2.id = t.id
               ) ;