Select first and last records in grouped results - Oracle 11g

Question

Say I have the following information in an Oracle 11g table:

Qty Production  order   Date and time
--- -----------------   ---------------
20  00000000000000001   12-JAN-14 00:02
20  00000000000000001   12-JAN-14 00:05
20  00000000000000001   12-JAN-14 00:07
20  00000000000000001   13-JAN-14 00:09
30  00000000000000002   12-JAN-14 00:11
30  00000000000000002   12-JAN-14 00:15
30  00000000000000002   12-JAN-14 00:20
30  00000000000000002   14-JAN-14 00:29

I would like to write a query that would return the following:

Qty Production  order   First               Last
--- -----------------   ---------------     ---------------
80  00000000000000001   12-JAN-14 00:02     13-JAN-14 00:09
120 00000000000000002   12-JAN-14 00:11     14-JAN-14 00:29

That is, the sum of the Qty column grouped by Production order, and the date/time of the first and last records for each Production order. I came up with a query that yielded this result:

Qty Production  order   First               Last
--- -----------------   ---------------     ---------------
80  00000000000000001   12-JAN-14 00:02     14-JAN-14 00:29
120 00000000000000002   12-JAN-14 00:02     14-JAN-14 00:29

Which means that the First and Last columns show the overall first and last date / time of the whole table. Please note that this is a dummy table. Sorry I am now allowed to write the actual query I came up with since work policies do not allow me to share it. Also, I tried with windowing functions such as rank() and row_number() but my user does not have enough privileges to do so. Any help or hints will be greatly appreciated.

How can a user not have privileges for a built-in function? Anyway, if you show your current query it might be simple to tweak it; you seem to be grouping at the right level already since your `qty` appears correct, so I'm curious how you're getting those date ranges at all. Particularly without using windowing functions. — Alex Poole, Jan 22 '14 at 20:59

score 3 · Accepted Answer · answered Jan 23 '14 at 05:02

I think you don't need rank() or row_number() in this case (at least with your sample data). If i am wrong then may be you could give more appropriate example. Anyway the following query returns what you need:

with t as (
select 20 Qty,
       '00000000000000001' Production_order,
       to_date('12-JAN-14 00:02',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 20 Qty,
       '00000000000000001' Production_order,
       to_date('12-JAN-14 00:05',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 20 Qty,
       '00000000000000001' Production_order,
       to_date('12-JAN-14 00:07',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 20 Qty,
       '00000000000000001' Production_order,
       to_date('13-JAN-14 00:09',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 30 Qty,
       '00000000000000002' Production_order,
       to_date('12-JAN-14 00:11',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 30 Qty,
       '00000000000000002' Production_order,
       to_date('12-JAN-14 00:15',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 30 Qty,
       '00000000000000002' Production_order,
       to_date('12-JAN-14 00:20',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual
union all
select 30 Qty,
       '00000000000000002' Production_order,
       to_date('14-JAN-14 00:29',
               'dd-MON-rr HH24:mi',
               'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
  from dual)
select sum(Qty) Total_Qty, 
       Production_order, 
       min(Date_and_time) First_Date_and_time,
       max(Date_and_time) Last_Date_and_time
from t
group by Production_order;

 TOTAL_QTY PRODUCTION_ORDER  FIRST_DATE_AND_TIME LAST_DATE_AND_TIME
---------- ----------------- ------------------- ------------------
        80 00000000000000001 12.01.2014 0:02:00  13.01.2014 0:09:00
       120 00000000000000002 12.01.2014 0:11:00  14.01.2014 0:29:00

I worked like a charm! Thank you SO very much! Any recommended reading to dive deeper into Oracle 11g? (Also, SQL in general) — gacanepa, Jan 23 '14 at 12:22
@gacanepa I am glad that my query was helpful, please accept it as an answer then. For Oracle I would personally recommend one of Tom Kyte's book. As of SQL in general there is [good reading](http://www.amazon.com/Mastering-SQL-Martin-Gruber/dp/0782125387/ref=pd_sim_sbs_b_1/192-9822020-5969158) by Martin Gruber. — Yaroslav Shabalin, Jan 23 '14 at 12:57

Select first and last records in grouped results - Oracle 11g

1 Answers1