Ordering output rows by the maximum value of three virtual columns

Поиск
Список
Период
Сортировка
От Guido Winkelmann
Тема Ordering output rows by the maximum value of three virtual columns
Дата
Msg-id 43779e1c$0$21955$9b4e6d93@newsread2.arcor-online.net
обсуждение исходный текст
Ответы Re: Ordering output rows by the maximum value of three virtual columns  (Bruno Wolff III <bruno@wolff.to>)
Список 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

В списке pgsql-novice по дате отправления:

Предыдущее
От: cjobbers@optonline.net
Дата:
Сообщение: Logging
Следующее
От: stig erikson
Дата:
Сообщение: Re: Install RPM Pre-req