Search Postgresql Archives

Re: Is ORDER BY in sub-query preserved when outer query is only projection?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Andreas:

On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh
<andreas@xxxxxxxxxx> 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.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux