Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBwC4-0002ap-ES for pgsql-sql@postgresql.org; Wed, 12 Sep 2012 23:18:04 +0000 Received: from mail-yw0-f46.google.com ([209.85.213.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBwC2-0008Na-74 for pgsql-sql@postgresql.org; Wed, 12 Sep 2012 23:18:03 +0000 Received: by yhmm54 with SMTP id m54so564056yhm.19 for ; Wed, 12 Sep 2012 16:18:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type; bh=XNWAe55M4d4dkj0n77xEi4Qk2u/vn0MVyNs6FGjxts8=; b=X3JtpU8q4C+VsqTFcs/R8eGHolCv686SEBop9Y2QCapQqxv84kbUW/EuzTckfDNUnu /mz4JNywOHv7g/ctneSDemgP/uerdxxFPBMDAUZFU945uzDHGuAzN9d1rl59vlE/C6UB yJpMhGdFWHC46iY6oqyUmdRXiwvuIqlgwXgKp0hIgA0rP5yjhjdSHD4cEV9vQAigDxfL QidbhoU6S86R8dkpwcv+p83KKAMNaiQWoM1fPSSqhxkGg5oePXtFLHycXiZ9pTEUG4C4 pjA2qzU98kjBwpHMRlsLlYSEPAqrD+Xmz7u/qM9y2wrDLwJVUVRnLNrs2u87B8IuqKp+ RSrA== Received: by 10.100.238.8 with SMTP id l8mr38693anh.21.1347491880867; Wed, 12 Sep 2012 16:18:00 -0700 (PDT) Received: from [187.64.171.216] ([187.64.171.216]) by mx.google.com with ESMTPS id w5sm19329895anl.10.2012.09.12.16.17.59 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 12 Sep 2012 16:18:00 -0700 (PDT) Message-ID: <50511852.2020605@gmail.com> Date: Wed, 12 Sep 2012 20:18:42 -0300 From: Rodrigo Rosenfeld Rosas User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.6esrpre) Gecko/20120817 Icedove/10.0.6 MIME-Version: 1.0 To: Samuel Gendler 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="------------010303050203080707000508" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201209/26 X-Sequence-Number: 36828 This is a multi-part message in MIME format. --------------010303050203080707000508 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > 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 > > --------------010303050203080707000508 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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> 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



--------------010303050203080707000508--