Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

On 13/09/12 09:44, Rodrigo Rosenfeld Rosas wrote:
> This is my first message in this list :)
>
> I need to be able to sort a query by column A, then B or C (which one
> is smaller, both are of the same type and table but on different left
> joins) and then by D.
>
> How can I do that?
>
> Thanks in advance,
> Rodrigo.
>
>
I created a script 'variable_sort_order.sql'...

DROP TABLE IF EXISTS tabc;

CREATE TABLE tabc
(
     id  serial PRIMARY KEY,
     a   int,
     b   int,
     c   int,
     d   int
);


INSERT INTO tabc (a, b, c, d)
VALUES (generate_series(1, 6),
         3 * random(),
         3 * random(),
         generate_series(1, 5));


SELECT
     *
FROM
     tabc t
ORDER BY
     t.a,
     LEAST(t.b, t.c),
     t.d
/**/;/**/

gavin=> \i variable_sort_order.sql
DROP TABLE
psql:variable_sort_order.sql:10: NOTICE:  CREATE TABLE will create 
implicit sequence "tabc_id_seq" for serial column "tabc.id"
psql:variable_sort_order.sql:10: NOTICE:  CREATE TABLE / PRIMARY KEY 
will create implicit index "tabc_pkey" for table "tabc"
CREATE TABLE
INSERT 0 30
  id | a | b | c | d
----+---+---+---+---
  25 | 1 | 0 | 3 | 5
   7 | 1 | 1 | 1 | 2
   1 | 1 | 3 | 2 | 1
  13 | 1 | 2 | 3 | 3
  19 | 1 | 2 | 2 | 4
   8 | 2 | 0 | 2 | 3
  14 | 2 | 0 | 2 | 4
  26 | 2 | 2 | 1 | 1
  20 | 2 | 1 | 2 | 5
   2 | 2 | 2 | 2 | 2
   3 | 3 | 0 | 2 | 3
  21 | 3 | 1 | 1 | 1
  27 | 3 | 1 | 3 | 2
  15 | 3 | 3 | 1 | 5
   9 | 3 | 3 | 2 | 4
   4 | 4 | 0 | 1 | 4
  10 | 4 | 3 | 0 | 5
  16 | 4 | 1 | 3 | 1
  22 | 4 | 1 | 1 | 2
  28 | 4 | 2 | 3 | 3
  11 | 5 | 0 | 1 | 1
  17 | 5 | 0 | 3 | 2
  23 | 5 | 1 | 1 | 3
   5 | 5 | 3 | 1 | 5
  29 | 5 | 3 | 2 | 4
  18 | 6 | 2 | 0 | 3
  12 | 6 | 1 | 1 | 2
  24 | 6 | 3 | 1 | 4
  30 | 6 | 1 | 3 | 5
   6 | 6 | 3 | 2 | 1
(30 rows)