postgresql order by field?

Question

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

stackoverflow.com/questions/1309624/… order by field is mysql, postgresql doesn't have that — user14215102
– user14215102, Commented Nov 15, 2020 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 ? — user14215102
– user14215102, Commented Nov 15, 2020 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 — Adrian Klaver
– Adrian Klaver, Commented Nov 15, 2020 at 17:40
What do you expect that non-existing field() function to do? — user330315
– user330315, Commented Nov 15, 2020 at 17:53

Slava Rozhnev · Accepted Answer · 2020-11-15 20:04:41Z

1

You query should be next:

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

answered Nov 15, 2020 at 20:04

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql order by field?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related