0

I want to get the result ordered by the same order of the list, but it keeps giving errors.

select "Usuario"."nombre", "Usuario"."codSis" 
from "public"."Usuario"
where "codSis" in (6, 8)
order by field(codSis ,6,8)

this code gives mi this error

ERROR:  column "codsis" does not exist
LINE 4: order by field(codSis ,6,8)
                       ^
HINT:  Perhaps you meant to reference the column "Usuario.codSis".
SQL state: 42703
Character: 112

but when I put this

select "Usuario"."nombre", "Usuario"."codSis" 
from "public"."Usuario"
where "codSis" in (6, 8)
order by field(Usuario.codSis ,6,8)

it gives me this error

ERROR:  column "Usuario.codSis" does not exist
LINE 4: order by field("Usuario.codSis" ,6,8)
                       ^
SQL state: 42703
Character: 112

then I tried this

select "Usuario"."nombre", "Usuario"."codSis" 
from "public"."Usuario"
where "codSis" in (6, 8)
order by field("Usuario"."codSis" ,6,8)

and it gave me this error

ERROR:  function field(integer, integer, integer) does not exist
LINE 4: order by field("Usuario"."codSis" ,6,8)
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 106
Rodrigo Salguero
  • 31
  • 5
  • 1
    https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql `order by field` is mysql, postgresql doesn't have that –  Nov 15 '20 at 17:15
  • What was that supposed to do anyway? 6 will order before 8 without any prodding, so do you just want `order by codSis` ? –  Nov 15 '20 at 17:16
  • You had two issues in play here. The first was the `order by field` statement not being a Postgres one. The second is identifier quoting. If an identifier is created in upper or mixed case by being quoted("codSis" ) then it needs to be used that way from then on. For more information see: [Identifiers](https://www.postgresql.org/docs/13/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) – Adrian Klaver Nov 15 '20 at 17:40
  • What do you expect that non-existing `field()` function to do? –  Nov 15 '20 at 17:53
  • Can you describe how you want the data to be sorted? – Laurenz Albe Nov 16 '20 at 08:12
  • I think that in the example I gave, it seemed like I just wanted it to be sorted in increasing order, I meant to be sorted in the order of any list of elements. @dratenik already solved my issue. I don't know how to close this question – Rodrigo Salguero Nov 16 '20 at 18:52

1 Answers1

1

You query should be next:

select
  "Usuario"."nombre",
  "Usuario"."codSis" 
from "public"."Usuario"
where "codSis" in (6, 8)
order by "Usuario"."codSis" asc;
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39