Re: Changing between ORDER BY DESC and ORDER BY ASC

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