[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL]
From: David Johnston <polobo () yahoo ! com>
Date: 2012-09-23 1:50:43
Message-ID: 7BBDED60-9F47-469E-B7DF-A391EAB4DF4B () yahoo ! com
[Download RAW message or body]
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.
[Attachment #3 (unknown)]
<html><head></head><body bgcolor="#FFFFFF"><div>On Sep 22, 2012, at 20:15, JORGE \
MALDONADO <<a href="mailto:jorgemal1960@gmail.com">jorgemal1960@gmail.com</a>> \
wrote:<br><br></div><div></div><blockquote type="cite"><div><font face="arial, \
helvetica, sans-serif">I have the following query:</font><div><font face="arial, \
helvetica, sans-serif"><br></font></div><div><p class="MsoNormal" \
style="margin-bottom:0.0001pt"><span lang="EN-US" style="font-size:9.5pt"><font \
face="arial, helvetica, sans-serif">SELECT</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">sem_clave, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, \
sans-serif">to_char(secc_esp_media.sem_fechareg,'TMMon-DD-YYYY') as \
sem_fechareg,</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, \
sans-serif">sem_seccion,</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, \
sans-serif">sem_titulo,</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">sem_enca, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">tmd_nombre, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">tmd_archivo, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">tmd_origen, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">gen_nombre, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">smd_nombre, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">prm_urlyoutube, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">prm_prmyoutube, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">prm_urlsoundcloud, \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">prm_prmsoundcloud \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">FROM secc_esp_media \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">INNER JOIN \
cat_tit_media ON tmd_clave = sem_titulo </font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">INNER JOIN \
cat_secc_media ON smd_clave = sem_seccion </font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">INNER JOIN \
cat_generos ON gen_clave = tmd_genero </font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">INNER JOIN \
parametros ON 1 = 1 </font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">WHERE \
</font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">smd_nombre = \
'SOMETHING' AND </font></span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span lang="EN-US" \
style="font-size:9.5pt"><font face="arial, helvetica, sans-serif">sem_fipub <= \
'SOME DATE' </font></span></p>
<span lang="EN-US" style="font-size:9.5pt;line-height:115%"><font face="arial, \
helvetica, sans-serif">ORDER BY sem_fipub DESC, sem_ffpub \
DESC </font></span></div><div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif"><br> </font></span></div><div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, sans-serif">I \
thought it was working fine until I noticed I needed to include a DISTINCT clause as \
follows:</font></span></div> <div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif"><br></font></span></div><div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif">SELECT DISTINCT ON (sem_clave) ......(the rest of the query is exactly \
the same as above)</font></span></div> <div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif"><br></font></span></div><div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif">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 ?</font></span></div> <div><span \
lang="EN-US" style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif"><br></font></span></div><div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif">Regards,</font></span></div> <div><span lang="EN-US" \
style="font-size:9.5pt;line-height:115%"><font face="arial, helvetica, \
sans-serif">Jorge Maldonado</font></span></div> \
</div></blockquote><div><br></div><div><br></div><div>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.</div><div><br></div><div>David \
J.</div><div><br></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic