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)