Sorting the field with empty value always at the bottom of the lists

SQL++
,
1

Hi guys,

How will I sort the field with empty value always at the bottom of the lists?
When doing ORDERY BY [ASC | DESC].

// My query

SELECT meta().id, firstName, lastName, metadata 
FROM `bluebonnet` WHERE `type`='contact'
ORDER BY firstName ASC
LIMIT 20

// Query Results ORDER BY firstName ASC.

[
  {
    "firstName": "",
    "id": "contact::01",
    "lastName": ""
  },
  {
    "firstName": "",
    "id": "contact::02",
    "lastName": ""
  },
  {
    "firstName": "Alice",
     "id": "contact::03",
    "lastName": "Doe"
  },
  {
    "firstName": "John",
    "id": "contact::04",
    "lastName": "Doe",
    "metadata": {
      "locationName": "location test"
    }
  }

// expected results something like this:.

[
  {
    "firstName": "Alice",
     "id": "contact::03",
    "lastName": "Doe"
  },
  {
    "firstName": "John",
    "id": "contact::04",
    "lastName": "Doe",
    "metadata": {
      "locationName": "location test"
    }
   {
    "firstName": "",
    "id": "contact::01",
    "lastName": ""
   },
   {
    "firstName": "",
    "id": "contact::02",
    "lastName": ""
   },
  }

Not just the firstName but, also sorting other fields.

Thanks :slight_smile:

2

DESC empty string always go to last of the string. ASC you can use one of the technique.

SELECT meta().id, firstName, lastName, metadata 
FROM `bluebonnet` WHERE `type`='contact'
ORDER BY CASE WHEN firstName = "" THEN [] ELSE firstName END  ASC,  
         CASE WHEN lastName = "" THEN [] ELSE lastName END  ASC
LIMIT 20;

If you are using 6.5+ You can try following too.

SELECT meta().id, firstName, lastName, metadata 
FROM `bluebonnet` 
WHERE `type`='contact'
ORDER BY DECODE(firstName, "", NULL, firstName) ASC NULLS LAST,
         DECODE(lastName, "", NULL, lastName) ASC NULLS LAST
LIMIT 20

To use Index order try following

CREATE INDEX ix1 ON bluebonnet( DECODE(firstName, "", [], firstName) , 
              DECODE(lastName, "", [], lastName) , 
              firstName, lastName, metadata )  WHERE type = "contact";

SELECT meta().id, firstName, lastName, metadata 
FROM `bluebonnet`
WHERE `type`='contact'  
        AND  DECODE(firstName, "", [], firstName) IS NOT NULL
ORDER BY DECODE(firstName, "", [], firstName) ASC,
          DECODE(lastName, "", [], lastName) ASC
LIMIT 20

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/orderby.html