From: David Johnston <polobo@yahoo.com>
To: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re:
Date: Sat, 22 Sep 2012 21:50:43 -0400
Message-ID: <7BBDED60-9F47-469E-B7DF-A391EAB4DF4B@yahoo.com> (raw)
In-Reply-To: <CAAY=A7_meqy6Ez80rNaAw+gZ1ZCkJOz=jxPw9dgTGR6Z1fg4kQ@mail.gmail.com>
References: <CAAY=A7_meqy6Ez80rNaAw+gZ1ZCkJOz=jxPw9dgTGR6Z1fg4kQ@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, jorgemal1960@gmail.com
Subject: Re:
In-Reply-To: <7BBDED60-9F47-469E-B7DF-A391EAB4DF4B@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox