Special ORDER BY
How can I order a result set by a special order of a column. For
example, let's say I have this query
SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)
Now I want to order the result set using c.etiqueta but with the order
of the values as you see in the IN clause.
How can I do that?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#333333">
You can use something like "CASE WHEN" .. and use a number to order
your data...<br>
like this<br>
<pre wrap=""><font color="#000099">SELECT *, CASE WHEN c.etiqueta = </font><font color="#000099">245</font> THEN <font color="#000099">1
</font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">130</font> THEN <font color="#000099">2 </font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = 240</font><font color="#000099"></font> THEN <font color="#000099">3 </font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">243</font> THEN <font color="#000099">4 </font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">246</font> THEN <font color="#000099">5 </font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">490</font> THEN <font color="#000099">6 </font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">630</font> THEN <font color="#000099">7</font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">730</font> THEN <font color="#000099">8</font>
<font color="#000099"> </font><font color="#000099">WHEN c.etiqueta = </font><font color="#000099">740</font> THEN <font color="#000099">9</font><font color="#000099"></font>
<font color="#000099"> end as newcolum FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)
ORDER BY </font><font color="#000099">newcolum</font>
</pre>
<br>
Em 19/06/2012 16:31, Martín Marqués escreveu:
<blockquote
cite="mid:CABeG9LtRiaAaEULLwxBoud+xcv+HknpFYioUaQYkC2ynq_4t+Q@mail.gmail.com"
type="cite">
<pre wrap="">How can I order a result set by a special order of a column. For
example, let's say I have this query
SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)
Now I want to order the result set using c.etiqueta but with the order
of the values as you see in the IN clause.
How can I do that?
</pre>
</blockquote>
<br>
<div class="moz-signature"><br>
<span style="font-size:8.0pt;font-family:Tahoma;
color:green"></span>
</div>
</body>
</html>
Helo
This is provided that the list is constant
SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
JOIN (values(245,1),(130,2), (240,3), (243,4), (246,5), (490,6),
(630,7), (730,8), (740,9)) as d(dato,orden) on (c.etiqueta =d.dato)
ORDRE BY d.orden
On Tue, Jun 19, 2012 at 2:31 PM, Martín Marqués <martin.marques@gmail.com>wrote:
How can I order a result set by a special order of a column. For
example, let's say I have this querySELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo)
WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740)Now I want to order the result set using c.etiqueta but with the order
of the values as you see in the IN clause.How can I do that?
--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate