Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

get rows where the last row finish off

DevxApr 29 2010 — edited May 3 2010
Hi, i have two tables AND would LIKE TO get data BY combining both.
here IS my data

WITH hist AS
(
  SELECT To_Date('4/23/2010','mm/dd/yyyy') dt, 999 alias, 'PROC' dom FROM dual UNION ALL
  SELECT To_Date('4/27/2010','mm/dd/yyyy') dt, 999 alias, 'LON' dom FROM dual UNION all

  SELECT To_Date('4/1/2010','mm/dd/yyyy') dt, 111 alias, 'SOC' dom FROM dual UNION all
  SELECT To_Date('4/10/2010','mm/dd/yyyy') dt, 111 alias, 'NAO' dom FROM dual UNION ALL

  SELECT To_Date('3/23/2010','mm/dd/yyyy') dt, 222 alias, 'PSE' dom FROM dual 
),
final AS
(
  SELECT To_Date('2/26/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL
  SELECT To_Date('4/22/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION all
  SELECT To_Date('4/26/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL
  SELECT To_Date('4/30/2010','mm/dd/yyyy') dt, 999 alias FROM dual UNION ALL

  SELECT To_Date('2/25/2010','mm/dd/yyyy') dt, 111 alias FROM dual UNION ALL

  SELECT To_Date('2/26/2010','mm/dd/yyyy') dt, 222 alias FROM dual UNION ALL
  SELECT To_Date('4/22/2010','mm/dd/yyyy') dt, 222 alias FROM dual UNION all
  SELECT To_Date('4/26/2010','mm/dd/yyyy') dt, 222 alias FROM dual 

)
 
the output should be as follow(without the extra blank line of course)
DT	      ALIAS   DOM
2/26/2010	999     PROC
4/22/2010	999     PROC
4/26/2010	999     LON
4/30/2010	999     LON
4/27/2010	999     LON
4/23/2010	999     PROC
                  
2/25/2010	111     SOC
4/1/2010	222     SOC
4/10/2010	222     NAO


2/26/2010	222     PSE
4/22/2010	222     PSE
4/26/2010	222     PSE
so what i am doing here is as follow, take one row in hist table (4/23) and join with final table and give me all rows in final table
where dt <= to the row in hist table and include the row from hist table.
this logic will give me rows 2/26/2010,4/22/10 4/23/2010

then the second row in hist table (4/27/2010) wiill get all rows
in final table that is <= to the current row and pick up the rows starting from the row > than the last row where the 4/23/2010 finished off
in this case the output will be 4/26/10, 4/27/2010(we need to include row from hist)

since there is no row in hist that is greater than 4/30/2010, this date will still be display and dom column value should be taking from the max date in hist
which is 4/27/2010. see output above

this sound a little confusing to explain but look at output of what to expect as output. the other ids should follow the same logic
can someone help write a query for this? thanks

Comments

644275
First, create a workspace on the drive that you want to save the file in and then try saving you file using File -> Save As and then select that workspace.
IHodgetts
Something else you might find useful is to have a shared (mapped) drive to store scripts centrally. See the post below:

882392
682072
thanks.....this information is useful
679279
hi

you can store the scripts in one of the workspace available in OATS.
you wont get a chance to create the work space in other drive then in which OATS is installed.

but if you need to make a back up of scripts then simply copy and paste it wherever you want them,and whenever you need to run the script then copy it and paste to one of the workspace of OATS.

hope will help you

regards
Umesh

Edited by: USoni on May 15, 2009 3:41 AM
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 30 2010
Added on Apr 29 2010
11 comments
2,107 views