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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

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,033 views