From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Sv: Re: Is ORDER BY in sub-query preserved when outer query is only projection? |
Date: | 2018-01-14 12:40:58 |
Message-ID: | VisenaEmail.1a.bdbcf39e20dac4d5.160f4ada518@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <
folarte(at)peoplecall(dot)com <mailto:folarte(at)peoplecall(dot)com>>:
Andreas:
On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh
<andreas(at)visena(dot)com> wrote:
> SELECT q.* FROM (
> SELECT comp.id, comp.name
> FROM company comp JOIN req r ON r.company_id = comp.id
> ORDER BY LOWER(comp.name) ASC
> ) AS q
> ORDER BY r.status ASC
>
> What I'm trying to do here is to order by some status (which may be only 1
of 3 values, for instance OPEN, IN_PROGRESS, CLOSED), then order by
company-name so I get results for each status sorted by company-name.
>
> Is this kind of sort stable, can I assume the pre-sorted result's order is
preserved so I achieve what I want?
I doubt it is mandated to be stable. But IIRC you can sort by a
non-returned field, so you should be able to do it in just one query (
http://sqlfiddle.com/#!17/aaa62/3 )
I would try
SELECT comp.id, comp.name
FROM company comp JOIN req r ON r.company_id = comp.id
ORDER BY r.status ASC, LOWER(comp.name) ASC
Francisco Olarte.
Thanks, but my real query is more complex and I need to sort on a custom
coposite type so I think I need an outer query for that, see my post
here: http://www.postgresql-archive.org/ORDER-BY-custom-type-td6000437.html
Do you see any solution sorting on a composite type without using an outer
query?
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2018-01-14 17:47:35 | Re: String comparison problem in select - too many results |
Previous Message | Francisco Olarte | 2018-01-14 12:30:29 | Re: Is ORDER BY in sub-query preserved when outer query is only projection? |