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

Ordering output rows by the maximum value of three virtual columns

From: Guido Winkelmann <guido(at)unknownsite(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Ordering output rows by the maximum value of three virtual columns
Date: 2005-11-13 20:12:07
Message-ID: 43779e1c$0$21955$9b4e6d93@newsread2.arcor-online.net (view raw or flat)
Thread:
Lists: pgsql-novice
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

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

Responses

pgsql-novice by date

Next:From: cjobbersDate: 2005-11-13 20:58:06
Subject: Logging
Previous:From: Bill DikaDate: 2005-11-13 14:19:05
Subject: Re: Application using PostgreSQL as a back end (experienced programmers please)

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