Home » SQL & PL/SQL » SQL & PL/SQL » Query Please
Query Please [message #266736] Tue, 11 September 2007 09:15 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi everyone!!

Please help me to find out a query on the data in the given file.

SERIAL_NO APPROVAL_LEVEL
--------- --------------
        1              1
        2              1
        3              2
        4              2
        5              3
        6              1
        7              1
        8              2
        9              2
       10              3
       11              3
       12              1
       13              2
       14              2
       15              3
       16              1
       17              1
       18              2
       19              2
       20              1
       21              2
       22              2
       23              3
       24              1
       25              1
       26              2
       27              2
       28              3
       29              3

from the above records, i want to find out the last records upto first occurence of 1.

so the output should be like


SERIAL_NO APPROVAL_LEVEL
--------- --------------
       24              1
       25              1
       26              2
       27              2
       28              3
       29              3

Thanks
NATESH

[Edit MC: include file into post]
  • Attachment: data.java
    (Size: 1.11KB, Downloaded 174 times)

[Updated on: Tue, 11 September 2007 10:30] by Moderator

Report message to a moderator

Re: Query Please [message #266739 is a reply to message #266736] Tue, 11 September 2007 09:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Sorry Don't think I'll be opening attachments of unknown origin
Re: Query Please [message #266749 is a reply to message #266739] Tue, 11 September 2007 09:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.java files are plain text files that are not interpreted by any (potentially) harmful application

[Edit: Why on earth did you call this file data.java???]

[Updated on: Tue, 11 September 2007 09:43]

Report message to a moderator

Re: Query Please [message #266765 is a reply to message #266736] Tue, 11 September 2007 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to find out the last records upto first occurence of 1.

first occurrence? In reverse order I think? In this case why 24 and not stop at 25?

In addition, next time find a meaningful title and post your Oracle version. It will be too bad for you if someone posts a solution that does not work in your version.

Regards
Michel

[Updated on: Tue, 11 September 2007 10:32]

Report message to a moderator

Re: Query Please [message #266782 is a reply to message #266736] Tue, 11 September 2007 11:34 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Assuming the requirement is to get all the rows starting from the first row of the last batch of contiguous approval_level=1 rows:

select * 
from appr a1, 
     (select serial_no
        from (select serial_no, rank() over (order by serial_no desc) as rn 
                from (select serial_no, approval_level,
                             lag(approval_level) over (order by serial_no) as prev_appr 
                        from appr)
                where approval_level=1 and prev_appr <> 1)
        where rn=1) a2
where a1.serial_no >= a2.serial_no

[Updated on: Wed, 12 September 2007 01:55] by Moderator

Report message to a moderator

Re: Query Please [message #266895 is a reply to message #266749] Tue, 11 September 2007 23:40 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Frank
By mistake it saved with .java extension. And, actually i mean the file contains only data, but not any code Smile.

Sorry for the inconvenience and Thanks for the Interest.

Natesh

[Updated on: Tue, 11 September 2007 23:41]

Report message to a moderator

Re: Query Please [message #266904 is a reply to message #266765] Tue, 11 September 2007 23:55 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Michel
Thanks for the advice about the subject.

To make it more clear,
It should check upto last 1 i.e search for the values less than 3 and upto 1, and after reaching to 1, if it finds any another number it should stop checking there.

Thanks
Natesh
Re: Query Please [message #266958 is a reply to message #266904] Wed, 12 September 2007 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    step1 as (
  3      select serial_no, approval_level,
  4             case 
  5                when    min(approval_level) over (order by serial_no desc) > 1 
  6                     or approval_level = 1
  7                then 0
  8                else 1
  9             end grp
 10      from t
 11    ),
 12    step2 as (
 13      select serial_no, approval_level,
 14             max(grp) over (order by serial_no desc) grp
 15      from step1
 16    )
 17  select serial_no, approval_level
 18  from step2
 19  where grp = 0
 20  order by serial_no
 21  /
 SERIAL_NO APPROVAL_LEVEL
---------- --------------
        24              1
        25              1
        26              2
        27              2
        28              3
        29              3

6 rows selected.

Regards
Michel
Re: Query Please [message #267115 is a reply to message #266736] Wed, 12 September 2007 09:11 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Michel,

I'm impressed you were able to interpret the requirements !!

Previous Topic: How can we rollback DDL changes (3 topics merged)
Next Topic: Runtime Select
Goto Forum:
  


Current Time: Wed Dec 07 06:43:53 CST 2016

Total time taken to generate the page: 0.06675 seconds