pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

(no subject)
2+ messages / 2 participants
[nested] [flat]

* (no subject)
@ 2012-09-23 00:15  JORGE MALDONADO <jorgemal1960@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: JORGE MALDONADO @ 2012-09-23 00:15 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re:
@ 2012-09-23 01:50  David Johnston <polobo@yahoo.com>
  parent: JORGE MALDONADO <jorgemal1960@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David Johnston @ 2012-09-23 01:50 UTC (permalink / raw)
  To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-09-23 01:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-23 00:15  JORGE MALDONADO <jorgemal1960@gmail.com>
2012-09-23 01:50 ` Re: David Johnston <polobo@yahoo.com>

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