Home » SQL & PL/SQL » SQL & PL/SQL » Query help please
Query help please [message #186021] Fri, 04 August 2006 14:54 Go to next message
wrightnn
Messages: 2
Registered: August 2006
Location: Atlanta, Georgia
Junior Member
Given the table...

Field1, Field2, Field3
1, a, 1pm
1, b, 2pm
1, c, 3pm
2, d, 4pm
1, e, 5pm
1, f, 6pm
1, g, 7pm
2, h, 8pm

How do I select field2 of the two rows where field 1 = 2 and the most recent in time row where field 1 = 1?

So my result set needs to look like...
d, c
h, g

Any help would be greatly appreciated.
Re: Query help please [message #186023 is a reply to message #186021] Fri, 04 August 2006 15:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The homework tutor is down the hall 2nd door on the right.

Show us (TINU) what you have tried and the results.
Explain why you are getting the results your results.
Re: Query help please [message #186028 is a reply to message #186023] Fri, 04 August 2006 16:14 Go to previous messageGo to next message
wrightnn
Messages: 2
Registered: August 2006
Location: Atlanta, Georgia
Junior Member
Well this is what i have so far, translated from the real thing to this context...

declare
  cursor field1is2 is
    select field1, field2, field3
    from table
    where field1=2;

begin
  spool theresults.txt;

  for i in field1is2
  loop
    select results.field2
         , i.field2
    from (
      select field2
      from table
      where field1 = 1
        and field3 = (select max(field3)
                      from table
                      where field1 = 1
                        and field3 < i.field3)
          ) results;
  end loop;

  spool off
end;


This doesn't wanna work though, yet. Can you spool inside a begin? I'm really looking for approach advice like "whoah this is way too complicated, here use this simple join..." or "yeah this will work, just keep debugging it" or "no you can't spool inside a begin, do this instead...".

All help is appreciated.
Re: Query help please [message #186034 is a reply to message #186028] Fri, 04 August 2006 21:42 Go to previous message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
You cannot spool inside of a pl/sql block. You need to start spooling before the block and stop after the block. However, unlesss you select into a variable and use dbms_output within the block to output that variable, you won't see any results. You will also need to set serveroutput on for dbms_output to work. Your select statement has more nested subqueries than it needs to. You need to avoid using reserved words, like table, for table names. The whole thing could be done much more simply in one sql select statement without a pl/sql block. I have provided some partial code below to give you the idea, with most of it deliberately removed, so that you can figure it out. Please read the sticky at the top of the forum for what we expect and follow those guidelines in the future, such as providing create table and insert statements, and your Oracle version.

-- test data:
 
SCOTT@10gXE> SELECT * FROM the_table ORDER BY field3
  2  /

    FIELD1 FIELD2 FIELD3
---------- ------ ------
         1 a      1pm
         1 b      2pm
         1 c      3pm
         2 d      4pm
         1 e      5pm
         1 f      6pm
         1 g      7pm
         2 h      8pm

8 rows selected.


-- your way corrected:
SCOTT@10gXE> set serveroutput on format wrapped
SCOTT@10gXE> spool theresults.txt
SCOTT@10gXE> declare
  2    ... -- variable that you select into
  3  begin
  4    dbms_output.put_line (' ');
  5    dbms_output.put_line ('RESULTS');
  6    dbms_output.put_line ('-------');
  7    for i in  
  8  	 (select ...
  9  	  from	 ...
 10  	  where  ...)
 11    loop
 12  	 select ...
 13  	 into	... -- variable that you select into
 14  	 from	... 
 15  	 where	...
 16  	 and	... =
 17  		(select ...
 18  		 from	...
 19  		 where	...
 20  		 and	...);
 21  	 dbms_output.put_line (...);
 22    end loop;
 23  end;
 24  /

RESULTS
-------
d, c
h, g

PL/SQL procedure successfully completed.

SCOTT@10gXE> spool off
SCOTT@10gXE> ed results.txt


-- simpler way:
SCOTT@10gXE> spool theresults.txt
SCOTT@10gXE> SELECT ...
  2  	    || ', ' ||
  3  	    (select ...
  4  	     from   ...   
  5  	     where  ...
  6  	     and    ... =
  7  		    (select ...
  8  		     from   ...
  9  		     where  ...
 10  		     and    ...))
 11  	    AS results
 12  FROM   ...
 13  WHERE  ...
 14  /

RESULTS
--------------
d, c
h, g

SCOTT@10gXE> spool off
SCOTT@10gXE> ed results.txt





Previous Topic: Trigger
Next Topic: Need help in query for finding search results
Goto Forum:
  


Current Time: Fri Dec 02 16:49:28 CST 2016

Total time taken to generate the page: 0.20966 seconds