How to query to pull all data in row based MIN and MAX service date

oraclequery

I'm very new to Oracle SQL especially in the Pentaho report builder. It lets you do some things and block others. PL/SQL is not available so I must do this in a query.

I need to be able to pull all the data in each row for a client for their MIN(service_date) and their MAX(service_date). Below are the table data and the result I'd like to get.

Table Data 

client_id | service_date  | other_stuff 
--------------------------------------- 
        1 | 2/24/2010     | Bob
        1 | 3/23/2010     | Jane
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 4/27/2005     | Leisha
        3 | 5/29/2005     | Tonay

Result data

client_id | service_date | other_stuff
--------------------------------------
        1 | 2/24/2010     | Bob
        1 | 4/23/2010     | Sam
        2 | 1/1/2000      | Julie
        2 | 2/2/2000      | Tina
        3 | 3/28/2005     | D'Shaun
        3 | 5/29/2005     | Tonay

Best Answer

There might be other better ways but does following work for you?

select client_id
      ,service_date
      ,other_stuff
  from (
        select client_id
              ,service_date
              ,other_stuff
              ,row_number() over (partition by client_id order by service_date) rn_min
              ,row_number() over (partition by client_id order by service_date desc) rn_max
          from tbl
       )
 where (rn_min = 1 OR rn_max = 1) ;
Related Question