On Sep 22, 2012, at 20:15, JORGE MALDONADO <jorgemal1...@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.
