ORDER BY ASC and DESC at the same time

Asked

Viewed 1,491 times

1

I need the data you have tr_color = success are ordered in a descending manner DESC and those who do not have it, that the column is empty, be ordered in an Ascending manner ASC.

I’m assembling the query this way.

SELECT mc.competencia,
CASE
   WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
   WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
   WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
END AS tr_color
FROM movimento_cobranca AS mc
WHERE mc.matricula = 2035
ORDER BY tr_color

And this returning me the moment the following result.

Competencia |  tr_color
-----------------------
0419              ""
0419              ""
0419              ""
0519              ""
0619              ""
0719              ""
0819              ""
0919              ""
1019              ""
1119              ""
1219              ""
0419              ""
0319              success
0118              success
0319              success
0219              success
0119              success
0218              success
0318              success
0418              success
0518              success
0618              success
0718              success
0818              success
0918              success
1018              success
1118              success
1218              success

I need to return so that those who possess Success stay in the following order.

0319 - 0219 - 0119 - 1218 - 1118 ...

And those who don’t Success remain the same.

EDIT SOLUTION

SELECT * FROM
  (
    SELECT mc.competencia,
           CASE
             WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
             WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
             WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
             END AS tr_color
    FROM movimento_cobranca AS mc
    WHERE mc.matricula = 2035
      AND COALESCE(mc.valor_pago, 0.0) > 0.0
    ORDER BY data_vencimento DESC
  ) AS p
UNION ALL
(
  SELECT mc.competencia,
         CASE
           WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
           WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
           WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
           END AS tr_color
  FROM movimento_cobranca AS mc
  WHERE mc.matricula = 2035
    AND COALESCE(mc.valor_pago, 0.0) = 0.0
  ORDER BY data_vencimento
)
sql postgresql

  • It’s the same field that they use @Rbz. Oh my doubt on how to do hahah

  • SE talcoisa USE campo1 OU -campo2 (note the sign of - ) - With case when or equivalent in pgsql (in Mysql you have IF, in T-SQL you have IIF in 2012)

  • You can select from select and sort by those fields as suggested by @Rbz

  • I had tried to make one UNION but without success, perhaps because of my lack of knowledge in SQL.

  • @Guilhermerigotti Union tb serves well, if you can separate the 2 sets. SELECT fields WHERE tr_color = Success ORDER BY field1 ASC UNION SELECT fields WHERE tr_color <> Success ORDER BY field2 DESC (Set to your DB)

  • How would the CASE ? CASE WHEN tr_color <> 'success' THEN .... what I would be in the THEN?

  • @Bacco I can’t do 2 ORDER BY no Postgres using UNION.

  • Try to separate the select with (SELECT ...) UNION (SELECT ...) - Rarely use pgsql, I don’t like, to tell the truth, I barely remember the specifics. I just really wanted to say something beforehand until the official answer comes

  • @Bacco I understand, because it is pgsql is really boring.

Show 4 more comments

3 answers

2


Test like this, if it works out I’ll comment on the answer.

SELECT * FROM 
(
  SELECT mc.competencia,
  (CASE
  WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
  WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
  WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
  END) AS tr_color
  FROM movimento_cobranca AS mc
  WHERE mc.matricula = 2035
  AND mc.valor_pago = COALESCE(mc.valor_devido, 0.0)
  ORDER BY tr_color DESC
  )
UNION ALL
  (
  SELECT mc.competencia,
  (CASE
  WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
  WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
  WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
  END) AS tr_color
  FROM movimento_cobranca AS mc
  WHERE mc.matricula = 2035
  AND mc.valor_pago <> COALESCE(mc.valor_devido, 0.0)
  ORDER BY tr_color
  )
) AS tableAlias

  • To avoid long discussions in the comments; the conversation was moved to the chat

1

Run the query below where a column was created just for sorting that turns the value of the competence in sorting negative by reaching the desired result.

SELECT mc.competencia,
  CASE
     WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
     WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
     WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
  END AS tr_color,
  CASE
     WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN mc.competencia
     WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN mc.competencia*-1
     WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN mc.competencia
  END AS campo_ordenacao

FROM movimento_cobranca AS mc
WHERE mc.matricula = 2035
ORDER BY tr_color,campo_ordenacao

Take the example http://sqlfiddle.com/#! 15/0578f/1

1

Another solution would be to repeat the CASE in the clause ORDER BY, example:

SELECT mc.competencia,
CASE
   WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 'success'
   WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN ''
   WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN 'danger'
END AS tr_color,
CASE
   WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN mc.competencia
   WHEN (COALESCE(mc.valor_pago, 0.0) = 0) THEN mc.competencia*-1
   WHEN (COALESCE(mc.valor_pago, 0.0) <> COALESCE(mc.valor_devido, 0.0)) THEN mc.competencia
END AS campo_ordenacao


FROM movimento_cobranca AS mc
WHERE mc.matricula = 2035
ORDER BY 
    CASE 
      --tr_success
      WHEN (COALESCE(mc.valor_pago, 0.0) = COALESCE(mc.valor_devido, 0.0)) THEN 0 
      ELSE 1 END
      ,campo_ordenacao

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.