Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

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) ;
share|improve this answer
1  
There are variations on this method, but this version is probably the simplest. Analytic/window functions are definitely the way to go. –  Noah Yetter Oct 7 '12 at 13:31

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.

share|improve this answer
    
I reviewd the oracle plan and saw that my query also contains a full table scan. I will investigate the performance further. –  miracle173 Oct 7 '12 at 13:10
    
perhaps a better plan: sqlfiddle.com/#!4/dc0ee/1 –  miracle173 Oct 7 '12 at 13:20
    
@LeighRiffel: as far as I can see both queries are semantically equivalent so a perfect optimizer should create the same plan for them. currently I am not sure which one would perform better on a larger dataset if the optimizer is not so perfect. –  miracle173 Oct 8 '12 at 15:03
    
@LeighRiffel: sorting the windows is costly. In the following there is only one client id with about 788K different service_dates : pastebin.com/PdQ85iM9 –  miracle173 Oct 8 '12 at 18:59
    
@LeighRiffel: both statements query the same table with the same (primary key) index created with the create statement of my post. but the second query does not use it. the table contains 788K rows with client_id=1 , other_stuff='dummy stuff' and different service_dates (so the first client_usage was some thousand years b.c.) –  miracle173 Oct 8 '12 at 20:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.