Re: BUG #4693: When I use the order by it gets me an error, but if i use it without order by it's a correct query.

The following bug has been logged online:

Bug reference: 4693
Logged by: Oscar Bejarano
Email address: obejaranoc(at)msn(dot)com
PostgreSQL version: 8.3.0
Operating system: Suse 10
Description: When I use the order by it gets me an error, but if i
use it without order by it's a correct query.
Details:

=======QUERY=========
select
r1.nombreestado,r1.nombreplantel,r1.clavepsp,r1.primerapellido,r1.segundoape
llido,r1.nombre,r1.sesion,r1.integracion,r1.cantidad_estudiantil,r1.estudian
til,((r1.sesion+r1.integracion+r1.estudiantil)/3) as promedio
from (
select
t1.clavepsp,t1.nombreestado,t1.nombreplantel,t1.nombre,t1.primerapellido,t1.
segundoapellido,
(select t2.calificacion from tbevaluaciones_terminadas t2 where
t2.evaluacion_tipo='1' and t2.clavepsp=t1.clavepsp) as sesion,
(select t3.calificacion from tbevaluaciones_terminadas t3 where
t3.evaluacion_tipo='3' and t3.clavepsp=t1.clavepsp) as integracion,
(select count(t4.clavepsp) from tbevaluaciones_terminadas t4 where
t4.clavepsp=t1.clavepsp and t4.evaluacion_tipo='2') as
cantidad_estudiantil,
(select avg(t5.calificacion) from tbevaluaciones_terminadas t5 where
t5.clavepsp=t1.clavepsp and t5.evaluacion_tipo='2') as estudiantil
from(
select distinct(a.clavepsp),c.nombre as nombreestado,d.nombre as
nombreplantel,a.primerapellido,a.segundoapellido,a.nombre from ctpsps a
left join ctestados c on a.claveestado_plantel=c.claveestado
left join ctplanteles d on a.claveplantel=d.claveplantel
where 1=1 and a.clavepsp in (select b.clavepsp from
tbevaluaciones_terminadas b where 1=1 and b.fecha <='31/12/2008')
order by 1,2,3,4,5,6

) t1

) r1 order by 1,2,3,4,5,6

================

Thanks,
Oscar

On Wed, Mar 4, 2009 at 2:05 PM, Oscar Bejarano <obejaranoc(at)msn(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      4693
> Logged by:          Oscar Bejarano
> Email address:      obejaranoc(at)msn(dot)com
> PostgreSQL version: 8.3.0
> Operating system:   Suse 10
> Description:        When I use the order by it gets me an error, but if i
> use it without order by it's a correct query.
> Details:
>
> =======QUERY=========

which ORDER BY is causing troubles to you? the one in ther outer query
or the one in the inner query?
can you provide at least the error message? a little test case would be useful

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157