Skip site navigation (1) Skip section navigation (2)

Re: Ordering output rows by the maximum value of three virtual columns

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Guido Winkelmann <guido(at)unknownsite(dot)de>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Ordering output rows by the maximum value of three virtual columns
Date: 2005-11-23 12:23:57
Message-ID: 20051123122357.GB7998@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, Nov 13, 2005 at 21:12:07 +0100,
  Guido Winkelmann <guido(at)unknownsite(dot)de> wrote:
> Hi,
> 
> I'm looking for a way to sort the output rows of a SELECT expressions by the
> maximum of three virtual columns of the output. Sorting it by one virtual
> column seems to be no problem:
> 
> SELECT
>   (<some subselect expression>) AS a,
>   <some more columns>
>   FROM <table>
>   ORDER BY a;
> 
> works fine.
> 
> Now, I have three different subselects, all of them positive integers, and
> I'd like the rows to be sorted by the maximimum of these three columns.
> I tried

In 8.1 there is a greatest function that you can use instead of the
case statements.

Your other issue has to do with what is allowed in order by. From the manual:
expression can be the name or ordinal number of an output column (SELECT list
item), or it can be an arbitrary expression formed from input-column values.

So you can't make expressions with output column names.

> 
> SELECT
>   (<some subselect expression>) AS a,
>   (<another subselect expression>) AS b,
>   (<a third subselect expression>) AS c,
>   <some more columns>
>   FROM <table>
>   ORDER BY 
>     CASE 
>       WHEN a > 
>         CASE 
>           WHEN 
>             b>c THEN b 
>           ELSE c 
>         END 
>       THEN a 
>       ELSE 
>         CASE 
>           WHEN 
>             b>c THEN b 
>           ELSE c 
>         END 
>     END;
> 
> but that'll tell me "ERROR:  column "a" does not exist".
> 
> The following:
> 
> SELECT
>   (<first subselect expression>) AS a,
>   (<second subselect expression>) AS b,
>   (<third subselect expression>) AS c,
>   CASE 
>     WHEN (<first subselect expression>) > 
>       CASE 
>         WHEN 
>           (<second subselect expression>)>(<third subselect expression>)
>         THEN (<second subselect expression>)
>         ELSE (<third subselect expression>)
>       END 
>     THEN (<first subselect expression>)
>     ELSE 
>       CASE 
>         WHEN 
>           (<second subselect expression>)>(<third subselect expression>)
>         THEN (<second subselect expression>)
>         ELSE (<third subselect expression>)
>       END 
>   END AS last_changed
>   <some more columns>
>   FROM <table>
>   ORDER BY last_changed;
> 
> works, but is very, very unelegant and takes a long time to execute even on
> a small table. I suspect there are more elegant and faster ways to this.
> 
> So, how can this be done better?
> 
>         Guido
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

In response to

pgsql-novice by date

Next:From: Raphael BauduinDate: 2005-11-23 14:51:38
Subject: myfunc_setvar and postgresql 8.1
Previous:From: Bruno Wolff IIIDate: 2005-11-23 12:17:16
Subject: Re: formating a select from a timestamp column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group