Re: Defining a Custom Sort Order in SQL

[Mike <koldark@xxxxxxxxx>]

Doesn't seem to work. Is this supposed to work on V5R3?

On 5/29/07, Gene Burns <burns.gene@xxxxxxxxx> wrote:
> You can use the CASE keyword in the order by clause
>
> ORDER BY CASE fieldname
> WHEN 'Filters' THEN 1
> ELSE 2
> END
>
>
> Gene
>
> On 5/29/07, Mike <koldark@xxxxxxxxx> wrote:
> >
> > I am having a hard time getting a custom sort working. The user wants
> > "Filters" listed first then everything else after that (they don't care
> > about order, but I figured listed as alpha). This is break logic on part
> > type. So here is the SQL statement I am trying
> >
> > SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN
> >   FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
> >   WHERE RPKEY = AKEY AND
> >         RPARTC = CBCODE AND
> >         RVEHKY IN
> >           (SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
> >             FROM QMFILES.GA560AP
> >             GROUP BY RVEHNO) AND
> >         RVEHNO = 2 AND RPARTC = 2
> > UNION
> > SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN FROM
> (
> > SELECT RVEHNO, RVEHKY, RPARTC, CBDESC, RPARTN, AMFGCD, ADESC, ABIN
> >   FROM QMFILES.GA560AP, QMFILES.GA600AP, QMFILES.GA122AP
> >   WHERE RPKEY = AKEY AND
> >         RPARTC = CBCODE AND
> >         RVEHKY IN
> >           (SELECT DISTINCT MAX(RVEHKY) AS RVEHKY
> >             FROM QMFILES.GA560AP
> >             GROUP BY RVEHNO) AND
> >         RVEHNO = 2 AND
> >         RPARTC <> 2
> >   ORDER BY CBDESC, RPARTC, RPARTN) AS TABLE
> >
> > I was hoping that the union work work which it does, but the other items
> > are
> > unsorted. Ideas?
> >
> > --
> > Mike Wills
> > http://www.linkedin.com/in/mikewills
> > Blog - http://mikewills.name
> > Podcasts - http://podcastmike.com
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.