Ordering output by custom order with GridDB

Question

I am trying to run a ORDER BY query but I want the output to be ordered according to a custom scheme. In SQL this is often done with a case system, but depending on the DB there are also other implementations. How would I achieve the sorting explained below with GridDB?

Lets assume my GridDB lists all the animals that are in a zoo and how many of each live there, I would like all the animals of which there are 4 or more, by size, smallest first (mouse), largest last (elephant).

Things I tried:

$query = $col->query("SELECT * WHERE count >= 4 ORDER BY CASE
                        WHEN 'mouse' THEN 1
                        WHEN 'cat' THEN 2
                        WHEN  'dog' THEN 3
                        WHEN  'elephant' THEN 4
                        ELSE 5
                        END");

$query = $col->query("SELECT * WHERE count >= 4 ORDER BY FIND_IN_SET(animal, 'mouse, cat, dog, elephant')";

In either case one receives an error that the input is not valid syntax.

score 2 · Accepted Answer · answered Jun 11 '20 at 11:29

GridDB's TQL doesn't support ORDER BY CASE but with multi-query you can perform this operation quickly/efficiently.

Please excuse my Python:

   queries=[]
   queries.append(col.query("select * where count >=4 and animal = 'mouse'"))
   queries.append(col.query("select * where count >=4 and animal = 'cat'"))
   queries.append(col.query("select * where count >=4 and animal = 'dog'"))
   queries.append(col.query("select * where count >=4 and animal = 'elephant'"))

   store.fetch_all(queries)

   for q in queries:
       rs = q.get_row_set()
       while rs.has_next():
           print(rs.next())

score 0 · Answer 2 · answered Oct 06 '20 at 06:19

You can use now use ORDER BY CASE with the V4.5CE SQL interface:

CREATE TABLE myTable (
  key INTEGER PRIMARY KEY,
  animal STRING,
  count INTEGER
);

INSERT INTO myTable VALUES(1, 'cat', 4); INSERT INTO myTable VALUES(2, 'dog', 1); INSERT INTO myTable VALUES(3, 'mouse', 6);

SELECT * FROM myTable WHERE count >= 4 ORDER BY CASE animal
                        WHEN 'mouse' THEN 1
                        WHEN 'cat' THEN 2
                        WHEN  'dog' THEN 3
                        WHEN  'elephant' THEN 4
                        ELSE 5
                        END;
-->
key | animal | count
----+--------+------
  3 | mouse  |     6
  1 | cat    |     4

Ordering output by custom order with GridDB

2 Answers2