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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828