pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
6+ messages / 4 participants
[nested] [flat]
* ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-12 21:44 Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rodrigo Rosenfeld Rosas @ 2012-09-12 21:44 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-12 21:53 Samuel Gendler <sgendler@ideasculptor.com>
parent: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Samuel Gendler @ 2012-09-12 21:53 UTC (permalink / raw)
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>; +Cc: pgsql-sql
you put a conditional clause in the order by statement, either by
referencing a column that is populated conditionally, like this
select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by 1,2, 5
or
select A, when B < C Then B else C end as condColumn, B, C, D
from ...
where ...
order by A,condColumn, D
or you can just put the conditional statement in the order by clause (which
surprised me, but I tested it)
select A, B, C, D
from ...
where ...
order by A,when B < C then B else C end, D
On Wed, Sep 12, 2012 at 2:44 PM, Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com
> 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.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-12 22:34 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Gavin Flower @ 2012-09-12 22:34 UTC (permalink / raw)
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-12 23:18 Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rodrigo Rosenfeld Rosas @ 2012-09-12 23:18 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-sql
Replied just to Samuel and forgot to include the list in my reply. Doing
that now, sorry...
Em 12-09-2012 18:53, Samuel Gendler escreveu:
> you put a conditional clause in the order by statement, either by
> referencing a column that is populated conditionally, like this
>
> select A, when B < C Then B else C end as condColumn, B, C, D
> from ...
> where ...
> order by 1,2, 5
>
> or
>
> select A, when B < C Then B else C end as condColumn, B, C, D
> from ...
> where ...
> order by A,condColumn, D
>
> or you can just put the conditional statement in the order by clause
> (which surprised me, but I tested it)
>
> select A, B, C, D
> from ...
> where ...
> order by A,when B < C then B else C end, D
Thank you for your insight on this, Samuel, and for your quick answer :)
But I don't think it would solve the issue I have.
I'm developing a query builder for a search engine.
The user is able to query any amount of available filters. And some
fields may have any number of aggregate fields.
So, suppose you're looking for an event sponsored by some company.
In the events records there could be some fields like Sponsor, Sponsor
2, Sponsor 3 and Sponsor 4. Yes, I know it is not a good design choice,
but this is how the system I inherited works.
So, in the Search interface, there is no way to build OR statements. So,
there is a notion of aggregate fields where Sponsor is the aggregator
one and the others are aggregates from Sponsor. Only Sponsor shows up in
the Search UI.
So, suppose the user wants to sort by event location and then by sponsor.
If there are multiple sponsors for a given event I want to be able to
sort by the one that would be indexed first.
How could I create a generic query for dealing with something like this?
Thank you,
Rodrigo.
>
>
>
> On Wed, Sep 12, 2012 at 2:44 PM, Rodrigo Rosenfeld Rosas
> <rr.rosas@gmail.com <mailto:rr.rosas@gmail.com>> 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.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-12 23:20 Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
parent: Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 1 reply; 6+ messages in thread
From: Rodrigo Rosenfeld Rosas @ 2012-09-12 23:20 UTC (permalink / raw)
To: Gavin Flower <GavinFlower@archidevsys.co.nz>; +Cc: pgsql-sql
Em 12-09-2012 19:34, Gavin Flower escreveu:
> 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.
>>
>>
> ...
> SELECT
> *
> FROM
> tabc t
> ORDER BY
> t.a,
> LEAST(t.b, t.c),
> t.d
> ...
Thank you Gavin, I was looking for this LEAST function for a long time.
I have tried MIN but it didn't work with strings.
I guess this will allow me to do what I want. Thank you so much!
Best,
Rodrigo.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D
@ 2012-09-17 04:52 Craig Ringer <ringerc@ringerc.id.au>
parent: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2012-09-17 04:52 UTC (permalink / raw)
To: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>; +Cc: Gavin Flower <GavinFlower@archidevsys.co.nz>; pgsql-sql
On 09/13/2012 07:20 AM, Rodrigo Rosenfeld Rosas wrote:
> Thank you Gavin, I was looking for this LEAST function for a long time.
> I have tried MIN but it didn't work with strings.
In SQL, "MIN" is an aggregate function. It actually does work with
strings, but only when used as an aggregate:
regress=# SELECT min(x.a) FROM ( VALUES ('blah'),('blah2'),('aaaa') ) x(a);
min
------
aaaa
(1 row)
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-09-17 04:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-12 21:44 ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2012-09-12 21:53 ` Samuel Gendler <sgendler@ideasculptor.com>
2012-09-12 23:18 ` Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2012-09-12 22:34 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2012-09-12 23:20 ` Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
2012-09-17 04:52 ` Craig Ringer <ringerc@ringerc.id.au>
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