Re: Defining a Custom Sort Order in SQL

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.