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

Question

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

Answer 1

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) ;

Answer 2

The following will work. You can check it at SQL Fiddle. The primary guarantees that there will be returned only one row per client_id and service_date. SQL Fiddle shows that the plan differs to that of the answer of @Narendra. The latter shows a full table scan. But he size of the test data set is very small.

create table client_usage(
  client_id number,
  service_date date,
  other_stuff varchar2(30) not null,
  constraint pk_client_usage 
    primary key(client_id,service_date)
);

insert into client_usage(client_id,service_date,other_stuff) 
  values(1,to_date('2/24/2010','mm/dd/yyyy'),' Bob ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(1,to_date('3/23/2010','mm/dd/yyyy'),' Jane ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(1,to_date('4/23/2010','mm/dd/yyyy'),' Sam ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(2,to_date('1/1/2000','mm/dd/yyyy'),' Julie ');
insert into client_usage(client_id,service_date,other_stuff)       
  values(2,to_date('2/2/2000','mm/dd/yyyy'),' Tina ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(3,to_date('3/28/2005','mm/dd/yyyy'),' D''Shaun ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(3,to_date('4/27/2005','mm/dd/yyyy'),' Leisha ');
insert into client_usage(client_id,service_date,other_stuff) 
  values(3,to_date('5/29/2005','mm/dd/yyyy'),' Tonay ');


select client_id,service_date,other_stuff
  from client_usage
  where (client_id,service_date) in(
    select client_id,min(service_date)
       from client_usage
       group by client_id
     union all select client_id,max(service_date)
       from client_usage
       group by client_id
  );

I tried both queries on different datasets with 1 000 000 rows: max_i is the number of different clients_ids, max_j is the number of entries (service_dates) per client. random means that the service_dates where inserted at random into the table, increasing or decreasing means that they were inserted in inreasing or decreasing order. The first method is my method, the second one is that of @Narendra.

random, max_i=10, max_j=100000
00:00:01.32 00:00:08.00
random, max_i=1000, max_j=1000
00:00:01.83 00:00:08.35
random, max_i=100000, max_j=10
00:00:05.80 00:00:10.45
inceasing, max_i=10, max_j=100000
00:00:01.45 00:00:08.80
inceasing, max_i=1000, max_j=1000
00:00:01.99 00:00:08.84
inceasing, max_i=100000, max_j=10
00:00:05.49 00:00:10.97
decreasing, max_i=10, max_j=100000
00:00:01.48 00:00:08.54
decreasing, max_i=1000, max_j=1000
00:00:01.98 00:00:08.59
decreasing, max_i=100000, max_j=10
00:00:05.58 00:00:11.04

Tom Kyte mentions that sorting in both directions brings additional performance degradiation.