Re: SQL problem with Order by

From: Stephan Born <stephan.born_at_beusen.de>
Date: Tue, 26 Oct 1999 10:36:23 +0200
Message-ID: <38156807.5BBEDC85_at_beusen.de>

> Hi all,
> I have a problem with the 'order by' clause in SQL. I have a query like
>
> select
> AWD.ITEM_TYPE,
> AWD.EMPLID,
> AWD.INSTITUTION
> FROM PS_STDNT_AWARDS AWD,
> PS_ITEM_TYPE_FA ITF
> WHERE AWD.EMPLID = 'XXXXX'
> AND AWD.INSTITUTION = 'YYYYY'
> AND AWD.AID_YEAR = '2000'
> AND ((AWD.AWARD_PERIOD = 'A') OR (AWD.AWARD_PERIOD = 'B'))
> AND ((AWD.AWARD_STATUS = 'A') OR (AWD.AWARD_STATUS = 'O'))
> AND ITF.PRINT_LTR_OPTION <> 'N'
> AND ITF.SETID = AWD.SETID
> AND ITF.AID_YEAR = '2000'
> AND ITF.ITEM_TYPE = AWD.ITEM_TYPE
> and itf.item_type in ('60', '30', '45', '90', '46')
> AND ITF.EFFDT = (SELECT MAX(EFFDT)
> FROM PS_ITEM_TYPE_FA ITF1
> WHERE ITF1.SETID = ITF.SETID
> AND ITF1.AID_YEAR = ITF.AID_YEAR
> AND ITF1.ITEM_TYPE = ITF.ITEM_TYPE
> AND ITF1.EFF_STATUS = 'A'
> AND ITF1.EFFDT <= sysdate)
> ORDER BY ITF.ITEM_TYPE
>
> where I want the output to come in a particular order of item types.
> Like
> 60, 30, 45, 90, 46.
>
> i.e. I want to produce the output according to a particular order in a
> list of values. Which is the way to do this? Any feedback is
> appreciated.

One way to solve this problem would be to introduce a new table with the following columns:

create table item_order
(

    item_type number,
    order number
);

now fill this table with the following data:

insert into item_order
select 60, 1 from dual
union
select 30, 2 from dual
union
select 45, 3 from dual
union
select 90, 4 from dual
union
select 46, 5 from dual;

Now you have to modify your select-statement: Join in the new table like this

select

awd.item_type,
awd.emplid,
awd.institution
from
  ITEM_TYPE IT,            -- <---------------
  ps_stdnt_awards awd,
        ps_item_type_fa itf
where   awd.emplid = 'xxxxx'
  and   awd.institution = 'yyyyy'
  and   awd.aid_year = '2000'
  and ((awd.award_period = 'a') or (awd.award_period = 'b'))
  and ((awd.award_status = 'a') or (awd.award_status = 'o'))
  and   itf.print_ltr_option <> 'n'
  and   itf.setid = awd.setid

  and itf.aid_year = '2000'
  and itf.item_type = awd.item_type
   and itf.item_type in ('60', '30', '45', '90', '46')   and itf.effdt = (select max(effdt)

                     from ps_item_type_fa itf1
                    where itf1.setid = itf.setid
                      and itf1.aid_year = itf.aid_year
                      and itf1.item_type = itf.item_type
                      and itf1.eff_status = 'a'
                      and itf1.effdt <= sysdate)
 AND IT.ITEM_TYPE = ITF.ITEM_TYPE    -- <-----------------
order by IT.ORDER                -- <-------------------

I didn't test the statement, but it should work for you.

I hope I could help you, let me know

Regards, Stephan

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------

Received on Tue Oct 26 1999 - 10:36:23 CEST