Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBvWY-0001jA-Qs for pgsql-sql@postgresql.org; Wed, 12 Sep 2012 22:35:11 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBvWV-0007kK-A7 for pgsql-sql@postgresql.org; Wed, 12 Sep 2012 22:35:08 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id q8CMYqwI029387 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 13 Sep 2012 10:35:00 +1200 Message-ID: <50510E0C.8080704@archidevsys.co.nz> Date: Thu, 13 Sep 2012 10:34:52 +1200 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:15.0) Gecko/20120827 Thunderbird/15.0 MIME-Version: 1.0 To: Rodrigo Rosenfeld Rosas CC: pgsql-sql@postgresql.org Subject: Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------070607040308020302050503" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201209/29 X-Sequence-Number: 36831 This is a multi-part message in MIME format. --------------070607040308020302050503 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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) --------------070607040308020302050503 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

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)

--------------070607040308020302050503--