Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TFbLP-0000YL-KX for pgsql-sql@postgresql.org; Sun, 23 Sep 2012 01:50:51 +0000 Received: from nm23.bullet.mail.sp2.yahoo.com ([98.139.91.93]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TFbLL-0002Cn-17 for pgsql-sql@postgresql.org; Sun, 23 Sep 2012 01:50:51 +0000 Received: from [98.139.91.61] by nm23.bullet.mail.sp2.yahoo.com with NNFMP; 23 Sep 2012 01:50:44 -0000 Received: from [72.30.22.33] by tm1.bullet.mail.sp2.yahoo.com with NNFMP; 23 Sep 2012 01:50:44 -0000 Received: from [127.0.0.1] by omp1061.mail.sp2.yahoo.com with NNFMP; 23 Sep 2012 01:50:44 -0000 X-Yahoo-Newman-Id: 8560.14474.bm@omp1061.mail.sp2.yahoo.com Received: (qmail 13767 invoked from network); 23 Sep 2012 01:50:43 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=u62u7JEqQQIbq+mkohfhDQl8cxUfyDHe8x1IAp2V73BY1eA1PZoadrIs4cDXHPtk0vaQzxrAyEKOUnZayIhoDMzvtH59Dh/cZWWzLs7Yp0v/gciFjirlJcBWu6DoPiWwZZ6eVuq7HRhGo2XyLXTqvTR5ha27oQxsRbQ3rTGkJEg= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1348365043; bh=lDqwlA0Px3a04mwLt9P+Ktm7ntC3I3bdcCIHOWolhZ8=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=NUqP95A/q6RHE8rfIyZnVQ3sMmfhw0arlq0AUscyz8Jw92Jijj1J1Isv01Nya7jVrLXctM0q0JZgdvy8zaAZI/EaiJiLCl/glAOymNrS2hcPQkuQJVAZLr+W/YcLMd1QS0cB+OXyEs7SuLOoSIGxcR3eNT1EW8ya8A37jyqmaSg= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: S7yyyBsVM1myYKRhIPUvC23D..PJ4BdXsYPeeceTgzDnUhZ a0D3FV2D4e_QAzsZ9R_hat1PDSSF4fuagiwPAkoTxX75_bNpfYKrAogQYaMe Np3R.67O1ECA3dnHwLMbRi3OdwhgTuemP55u4qLyC15lwG3IcKlGslz8gD06 m_g7F9AdYfYq.4r0Atu3pHfEcMqPxXgu_dWnZWGkikrAmbAsah_uH0cdQVbo YzrXEeAsYwnc8L4zXNLplr020eq1__5bkMI2yNbGZjjTCc2.0mQBDAu_QAge TjSEzeL_CHIaYq4eKvlfj4v3VXHg2aIoBy2XRYjD_np4k4SIAp6fu2_bxXfZ .JCW6chFwm4PXcdYWVopLgyoV23YlYzNhdorAF2y3XNHrZBrKFqx.4KckCaD JgWjO8Nh35Z0PJYxqd35XfOFaa6AUEiAZClsB X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from [192.12.17.101] (polobo@24.93.23.188 with xymcookie) by smtp121-mob.biz.mail.gq1.yahoo.com with SMTP; 23 Sep 2012 01:50:43 +0000 UTC References: In-Reply-To: Mime-Version: 1.0 (1.0) Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary=Apple-Mail-5C4AB616-111B-4FE4-BBC5-38159BBEC49E Message-Id: <7BBDED60-9F47-469E-B7DF-A391EAB4DF4B@yahoo.com> Cc: "pgsql-sql@postgresql.org" X-Mailer: iPad Mail (9B206) From: David Johnston Subject: Re: Date: Sat, 22 Sep 2012 21:50:43 -0400 To: JORGE MALDONADO X-Pg-Spam-Score: -2.5 (--) X-Archive-Number: 201209/54 X-Sequence-Number: 36856 --Apple-Mail-5C4AB616-111B-4FE4-BBC5-38159BBEC49E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii On Sep 22, 2012, at 20:15, JORGE MALDONADO wrote: > I have the following query: >=20 > SELECT > sem_clave, > to_char(secc_esp_media.sem_fechareg,'TMMon-DD-YYYY') as sem_fechareg, > sem_seccion, > sem_titulo, > sem_enca, > tmd_nombre, > tmd_archivo, > tmd_origen, > gen_nombre, > smd_nombre, > prm_urlyoutube, > prm_prmyoutube, > prm_urlsoundcloud, > prm_prmsoundcloud > FROM secc_esp_media > INNER JOIN cat_tit_media ON tmd_clave =3D sem_titulo > INNER JOIN cat_secc_media ON smd_clave =3D sem_seccion > INNER JOIN cat_generos ON gen_clave =3D tmd_genero > INNER JOIN parametros ON 1 =3D 1 > WHERE > smd_nombre =3D 'SOMETHING' AND > sem_fipub <=3D 'SOME DATE' > ORDER BY sem_fipub DESC, sem_ffpub DESC=20 >=20 > I thought it was working fine until I noticed I needed to include a DISTIN= CT clause as follows: >=20 > SELECT DISTINCT ON (sem_clave) ......(the rest of the query is exactly the= same as above) >=20 > But, when I run it, I get a message telling me that I need an ORDER BY the= field "sem_clave" which is the field in the DISTINCT clause. How can I solv= e this issue without affecting the ORDER BY it already has ? >=20 > Regards, > Jorge Maldonado Since you are forced to include the ON field(s) first in the ORDER BY if you= want a different final sort order you will have to use either a sub-select o= r a CTE/WITH to execute the above query then in the outer/main query you can= perform a second sort. David J. --Apple-Mail-5C4AB616-111B-4FE4-BBC5-38159BBEC49E Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=utf-8
On Sep 22, 2012, at 20:15, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

I have the following query:

SELECT

sem_clave,

to_char(secc_esp_media.sem_fechareg,'TMMon-DD-YYYY') as sem_fechareg,

sem_seccion,

sem_titulo,

sem_enca,

tmd_nombre,

tmd_archivo,

tmd_origen,

gen_nombre,

smd_nombre,

prm_urlyoutube,

prm_prmyoutube,

prm_urlsoundcloud,

prm_prmsoundcloud

FROM secc_esp_media

INNER JOIN cat_tit_media ON tmd_clave = sem_titulo

INNER JOIN cat_secc_media ON smd_clave = sem_seccion

INNER JOIN cat_generos ON gen_clave = tmd_genero

INNER JOIN parametros ON 1 = 1

WHERE

smd_nombre = 'SOMETHING' AND

sem_fipub <= 'SOME DATE'

ORDER BY sem_fipub DESC, sem_ffpub DESC 

I thought it was working fine until I noticed I needed to include a DISTINCT clause as follows:

SELECT DISTINCT ON (sem_clave) ......(the rest of the query is exactly the same as above)

But, when I run it, I get a message telling me that I need an ORDER BY the field "sem_clave" which is the field in the DISTINCT clause. How can I solve this issue without affecting the ORDER BY it already has ?

Regards,
Jorge Maldonado


Since you are forced to include the ON field(s) first in the ORDER BY if you want a different final sort order you will have to use either a sub-select or a CTE/WITH to execute the above query then in the outer/main query you can perform a second sort.

David J.

--Apple-Mail-5C4AB616-111B-4FE4-BBC5-38159BBEC49E--