Sorting using FIELD

I am working with Laravel and migrating from Mysql to Cockroach. The below query is not working in Cockroach DB. Can anyone suggest any alternative…

select * from accounts order by   FIELD('id', 3,1,2,4)
Error:  unknown function: field()```

Hi @kishoresai438,

The FIELD function isn’t something that we support, we try to be compatible with Postgres and that appears to be a function that is used in MySQL.

We do have a function called array_position which returns the index of the first occurrence of elem in array.

The syntax would look something like this ORDER BY array_position(array[your,values], id).

One potential caveat would be that if a value isn’t in the array it’ll end up sorting first, but you could work around that by doing something like ORDER BY COALESCE(array_position(array[your,values], id), 999);

We have documentation on using the array functions here.

Let me know if you have any other questions.



FYI I’ve create an issue to document workarounds for FIELD here

1 Like