Re: Defining a Custom Sort Order in SQL

["Gene Burns" <burns.gene@xxxxxxxxx>]

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.