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

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

På mandag 15. januar 2018 kl. 19:04:51, skrev David G. Johnston <david.g.johnston@xxxxxxxxx>:

On Sun, Jan 14, 2018 at 5:40 AM, Andreas Joseph Krogh <andreas@xxxxxxxxxx> wrote:

På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <folarte@xxxxxxxxxxxxxx>:

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
 

 

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

andreas@xxxxxxxxxx

www.visena.com