Thread: Changing between ORDER BY DESC and ORDER BY ASC

Is there an easy way to write one single query that can alternate
between ASC and DESC orders?  Ex:

CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count integer,
_sortDesc boolean)
RETURNS SETOF text AS
$BODY$
                SELECT
                               something
                FROM
                               whatever
                WHERE
                               whatever
                ORDER BY
                                another_column
                OFFSET $1 LIMIT $2
                                ($4 = true ? 'DESC' : 'ASC');
$BODY$
LANGUAGE 'sql' VOLATILE;

I can think of a few ways, but I am hoping for something more elegant.
1) In my case another_column is numeric, so I could multiple by negative
one if I want it in the other order.  Not sure what this does to the
optimizer if the column is indexed or not.
2) I could write the statement twice, once with ASC and once with DESC,
and then use IF/ELSE structure to pick one.
3) I could generate the statement dynamically.

I am hoping there is some super secret extension that can handle this.
This seems like one of those foolish things in SQL, where it is too
declarative.  ASC and DESC should be parameters to order by, not a part
of the syntax.  But I digress... any other suggestions?

On Aug 15, 2008, at 12:35 PM, William Garrison wrote:
> Is there an easy way to write one single query that can alternate
> between ASC and DESC orders?  Ex:
>
> CREATE OR REPLACE FUNCTION GetSomeStuff(_start integer, _count
> integer, _sortDesc boolean)
> RETURNS SETOF text AS
> $BODY$
>                SELECT
>                               something
>                FROM
>                               whatever
>                WHERE
>                               whatever
>                ORDER BY
>                                another_column
>                OFFSET $1 LIMIT $2
>                                ($4 = true ? 'DESC' : 'ASC');
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
> I can think of a few ways, but I am hoping for something more elegant.
> 1) In my case another_column is numeric, so I could multiple by
> negative one if I want it in the other order.  Not sure what this
> does to the optimizer if the column is indexed or not.

In my experience, it's pretty rare for an index to be used to satisfy
an ORDER BY.

> 2) I could write the statement twice, once with ASC and once with
> DESC, and then use IF/ELSE structure to pick one.
> 3) I could generate the statement dynamically.
>
> I am hoping there is some super secret extension that can handle
> this.  This seems like one of those foolish things in SQL, where it
> is too declarative.  ASC and DESC should be parameters to order by,
> not a part of the syntax.  But I digress... any other suggestions?

None that I can think of, unfortunately. It might not be horribly
hard to allow plpgsql to use a variable for ASC vs DESC; that might
be your best bet.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828