pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
Date: Thu, 13 Sep 2012 10:34:52 +1200
Message-ID: <50510E0C.8080704@archidevsys.co.nz> (raw)
In-Reply-To: <CAGmv+wKCpc9VeyYp6nS9WL1vrtX=3+i87mFVMP4sEpB1LyPGQA@mail.gmail.com>
References: <CAGmv+wKCpc9VeyYp6nS9WL1vrtX=3+i87mFVMP4sEpB1LyPGQA@mail.gmail.com>

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)

view thread (6+ messages)  latest in thread

Message-ID: <50510E0C.8080704@archidevsys.co.nz>
Permalink:  ../50510E0C.8080704@archidevsys.co.nz/
Also on:    postgresql.org/message-id/50510E0C.8080704@archidevsys.co.nz

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: GavinFlower@archidevsys.co.nz, rr.rosas@gmail.com
  Subject: Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
  In-Reply-To: <50510E0C.8080704@archidevsys.co.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox