Home » SQL & PL/SQL » SQL & PL/SQL » First row one column and last row other column
First row one column and last row other column [message #212179] Thu, 04 January 2007 03:34 Go to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi,
I need a query to result as,

Table is

Shipment_no action_no load_date unload_date
1 100 01/10/2005*** 01/11/2005
1 101 01/11/2005 01/12/2005
1 102 01/12/2005 01/01/2007
1 103 01/01/2005 01/02/2007***

Result needed,

Shipment_no load_date unload_date
1 01/10/2005 01/02/2007

Result is of
(Shipment_no)(first action load_date)(last action unload_date)


Thanks for any kind of help.
Amol

[Updated on: Thu, 04 January 2007 03:42]

Report message to a moderator

Re: First row one column and last row other column [message #212186 is a reply to message #212179] Thu, 04 January 2007 04:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You have given no definition of 'first action load date' or 'last action unload date' so I've made up my own sort. I based my sort on action_no. Next time, provide your create table and insert statements. Here's what I came up with (forget the WITH clause, that's because I had to simulate your table):
With yourtable AS ( select 1                                  shipment_no
                         , 100                                action_no
                         , to_date('01/10/2005','mm/dd/yyyy') load_date
                         , to_date('01/11/2005','mm/dd/yyyy') unload_date 
                    from   dual
                    union all
                    select 1                                  shipment_no
                         , 101                                action_no
                         , to_date('01/11/2005','mm/dd/yyyy') load_date
                         , to_date('01/12/2005','mm/dd/yyyy') unload_date 
                    from dual
                    union all
                    select 1                                  shipment_no
                         , 102                                action_no
                         , to_date('01/12/2005','mm/dd/yyyy') load_date
                         , to_date('01/01/2007','mm/dd/yyyy') unload_date 
                    from dual
                    union all
                    select 1                                  shipment_no
                         , 103                                action_no
                         , to_date('01/01/2005','mm/dd/yyyy') load_date
                         , to_date('01/02/2007','mm/dd/yyyy') unload_date 
                    from dual
                  )
select distinct
       shipment_no
     , first_value(load_date)   OVER ( PARTITION BY shipment_no 
                                       ORDER     BY action_no ASC  ) load_date
     , first_value(unload_date) OVER ( PARTITION BY shipment_no 
                                       ORDER     BY action_no DESC ) unload_date
from   yourtable
/

When I ran this, I got:
SQL> @orafaq

SHIPMENT_NO LOAD_DATE UNLOAD_DA
----------- --------- ---------
          1 10-JAN-05 02-JAN-07



MHE
Re: First row one column and last row other column [message #212189 is a reply to message #212186] Thu, 04 January 2007 04:06 Go to previous message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi Maaher,

Thanks a lot for your quick help.

Thanks,
Amol
Previous Topic: How to use interval datatype in Oracle
Next Topic: Advanced queries in sql
Goto Forum:
  


Current Time: Thu Dec 08 20:25:11 CST 2016

Total time taken to generate the page: 0.14207 seconds