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.

Thanks,

Ron

FYI I’ve create an issue to document workarounds for FIELD here https://github.com/cockroachdb/docs/issues/4962

1 Like