Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Andreas Joseph Krogh
Date:
Hi.
 
I have this query:
 
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?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

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

From
Francisco Olarte
Date:
Andreas:

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

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

From
Andreas Joseph Krogh
Date:
På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <folarte@peoplecall.com>:
Andreas:

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

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

From
Haroldo Stenger
Date:
hi, maybe selecting the relevant fields to order-by alongside (next to) the row()'ed part, they will be selected  "twice", but you'll be able to reference them.

regards


Haroldo

2018-01-14 9:40 GMT-03:00 Andreas Joseph Krogh <andreas@visena.com>:
På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <folarte@peoplecall.com>:
Andreas:

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

Attachment

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

From
"David G. Johnston"
Date:
On Sun, Jan 14, 2018 at 5:40 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <folarte@peoplecall.com>:
Andreas:

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

 
Do you see any solution sorting on a composite type without using an outer query?


​Tacking on ORDER BY to an inner query is generally not the right thing to do.

​What can you not write:

SELECT q.* FROM () AS q
ORDER BY lower(q.name)​ ASC, q.status ASC
?

Also, ORDER BY r.status ASC in you original query shouldn't work - r is not visible at that point, only q is.

David J.

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

From
Andreas Joseph Krogh
Date:
På mandag 15. januar 2018 kl. 19:04:51, skrev David G. Johnston <david.g.johnston@gmail.com>:
On Sun, Jan 14, 2018 at 5:40 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <folarte@peoplecall.com>:
Andreas:

On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh
<andreas@visena.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
 
 
Do you see any solution sorting on a composite type without using an outer query?
 
 
​Tacking on ORDER BY to an inner query is generally not the right thing to do.
 
​What can you not write:
 
SELECT q.* FROM () AS q
ORDER BY lower(q.name)​ ASC, q.status ASC
?
 
This is what I ended up doing.
 
Also, ORDER BY r.status ASC in you original query shouldn't work - r is not visible at that point, only q is.
 
Yea, this was a thinko from my part, I meant q.status.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment