Thread: 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

You can use something like "CASE WHEN" .. and use a number to order
    your data...
    like this
    SELECT *, CASE WHEN c.etiqueta = 245 THEN 1
        WHEN c.etiqueta = 130 THEN 2
        WHEN c.etiqueta = 240 THEN 3
        WHEN c.etiqueta = 243 THEN 4
        WHEN c.etiqueta = 246 THEN 5
        WHEN c.etiqueta = 490 THEN 6
        WHEN c.etiqueta = 630 THEN 7
        WHEN c.etiqueta = 730 THEN 8
        WHEN c.etiqueta = 740 THEN 9
  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 newcolum


    Em 19/06/2012 16:31, Martín Marqués escreveu:
    <blockquote
cite="mid:CABeG9LtRiaAaEULLwxBoud+xcv+HknpFYioUaQYkC2ynq_4t+Q@mail.gmail.com"
      type="cite">
      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?





      <span style="font-size:8.0pt;font-family:Tahoma;
        color:green">

--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate