Order by using FIELD

SQL++
1

Hello,

I have a situation where I want to order a list taking into consideration an enumeration of strings, where I want the submissions with status==“pending” come first, then with status approved and then with status rejected. I saw an example using SQL with the FIELD function. In my case I have this:

SELECT *
FROM brandbassador submissions
JOIN brandbassador users ON KEYS ‘User::’ || submissions.userId
WHERE submissions.docType = 'Submission’
AND users.docType = 'User’
AND submissions.brandId=$1 AND submissions.network is not missing AND submissions.missionId=$2
ORDER BY FIELD(submissions.status, ‘pending’, ‘approved’, ‘rejected’), submissions.created_at DESC

But it returns the following error:

“ERROR: Wrong number of arguments to function FIELD. - at )”

Any way of doing this type of order by?

Thanks for any help.

2

You can use a CASE expression to map those statuses to integers, say 0, 1, 2. See CASE in the N1QL documentation.

2 Likes
3

That solved the problem, thanks!

1 Like