Home » SQL & PL/SQL » SQL & PL/SQL » sql max
sql max [message #410889] Tue, 30 June 2009 23:24 Go to next message
blessonin
Messages: 2
Registered: June 2009
Location: India
Junior Member
I have a doubt in oracle,my scenario is i have a table called
receipt_details which contains the receipt no,and the receipt
status,and receipt created date,receipt status means,the
status of the receipt eaither it may be issued or cancelled
or blank,Suppose there are 100 receipts that is created in a
month ,so it may happen that 1 to 25 is in issued status and 25,27
are cancelled receipt and 27 to 50 are are in issue receipt,and 50 to 100
is in blank status ,now my requirement is to fetch these recipts from
the database and dispaly in a report in the below format.


used 1 to 25
cancelled 25 to 27
used 27 to 50
unused 50 to 100

The problem i am facing is ,if i am making a query like select
min (d.receipt_status) ,max(d.receipt_status) from receipt details
d where d.receipt_no between 1 and 100 means it will show from 1 to 27
but i want to break it and just show it above is it possible in query
Re: sql max [message #410900 is a reply to message #410889] Wed, 01 July 2009 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=8531

Please read OraFAQ Forum Guide, especially "Posting guidelines" section.

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: sql max [message #410955 is a reply to message #410900] Wed, 01 July 2009 04:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is a pretty frequently asked question, but the repetition of statuses in the list provides a slight complication.

Here's a way of doing it:
create table test_238 (id  number, status varchar2(1));

insert into test_238 
select level,
       case when level >= 50 then 'B' when level in (25,26,27) then 'C' else 'I' end 
from dual 
connect by level <=100;

select distinct
       status
      ,min(id) over (partition by grp order by id rows between unbounded preceding and unbounded following) min_id
      ,max(id) over (partition by grp order by id rows between unbounded preceding and unbounded following) max_id
from (select id
            ,status
            ,sum(change) over (order by id) grp
      from (select id
                  ,status
                  ,case when status != lag(status) over (order by id) then 1 else 0 end change
            from test_238
           )
     )
order by min_id;


The trick is this line here:
case when status != lag(status) over (order by id) then 1 else 0 end

This flags up when the status has changed from the previous row.
By creating a runing total of this value, you cansplit the ids into distinct, identifiable groups. All you need to do then is to get the minimum and maximum value from each group, and you're home.

[Updated on: Wed, 01 July 2009 06:02] by Moderator

Report message to a moderator

Re: sql max [message #410970 is a reply to message #410955] Wed, 01 July 2009 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't analyze your query but as I use to say to myself: "il you have analytical function and distinct" then something is wrong.

Is not (generally speaking):
"select distinct f(x) over(partition by g order by o rows between unbounded preceding and unbounded following)"
the same thing than
"select f(x) ... group by g"
?

Regards
Michel
Re: sql max [message #410980 is a reply to message #410970] Wed, 01 July 2009 07:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's a very good point - I'd not really thought of it that way.
I can't see a situation where that wouldn't be the case.

I imagine there would be a performance gain too, from the removal of the analytics.
select status
      ,min(id)  min_id
      ,max(id)  max_id
from (select id
            ,status
            ,sum(change) over (order by id) grp
      from (select id
                  ,status
                  ,case when status != lag(status) over (order by id) then 1 else 0 end change
            from test_238
           )
     )
group by status,grp     
order by min_id;
.
Previous Topic: High Query Execution Time
Next Topic: select query
Goto Forum:
  


Current Time: Fri Dec 09 13:35:01 CST 2016

Total time taken to generate the page: 0.18727 seconds