question

sathish avatar image
sathish asked Erick Ramirez answered

How do I get results in the same order as IN() query without sorting?

Hi,
Mine is a simple table

CREATE TABLE table1 (
    data2 bigint PRIMARY KEY,
    data1 list<int>
)

and my query is

SELECT * FROM table1 WHERE data2 IN (3016254749,1429742482,194194093);

I get result as (Actual Result)

 data2      | data1
------------+-------------
  194194093 |  920,39,195
 1429742482 | 140,840,154
 3016254749 |  416,133,180

But what I need is (Expected Result)

 data2      | data1
------------+-------------
 3016254749 |  416,133,180
 1429742482 | 140,840,154
  194194093 |  920,39,195

I need result as in my query order. Could this be possible in cql? if yes then could you please explain it?

cql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

2 Answers

starlord avatar image
starlord answered Erick Ramirez edited

Hi Sathish,

Unfortunately you cannot sort the only PK column in descending order.

Ideally you'll have another column in the table that can serve as the key, then you can utilize data2 as a clustering column stored in the desired order, for example, you could get the results you are after with the following table:

CREATE TABLE keyspace1.table3 (data1 list<int> , data2 bigint, data3 text, PRIMARY KEY (data3, data2) )WITH CLUSTERING ORDER BY (data2 desc);

In this scenario, you'd have data2 as a clustering column and records would be automatically stored in descending order:

select * from keyspace1.table3;

 data3 | data2      | data1
-------+------------+-----------------
 test3 | 3016254749 | [416, 133, 180]
 test2 | 1429742482 | [140, 840, 154]
 test1 | 194194093  | [920, 39, 195]

If such a table definition isn't possible, then you'd need something like a Solr index on the column to be able to order the results in descending order, however accomplishing the task with a proper table definition is the recommended way to go involving the least amount of overhead with the best performance.

Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image
Erick Ramirez answered

Sorting only applies to rows within a single partition. It is not possible to override the order of the results from a multi-partition query.

When retrieving multiple partitions, the result is displayed in order based on the equivalent token values of the partition keys. Using your example:

cqlsh:getvaxxed> SELECT * FROM inkeys WHERE pkey IN (3016254749,1429742482,194194093);

 pkey       | col1
------------+------
  194194093 |    3
 1429742482 |    2
 3016254749 |    1

the result is sorted in ascending order of the token values of the partition keys:

cqlsh:getvaxxed> SELECT TOKEN(pkey), pkey, col1 FROM inkeys WHERE pkey IN (3016254749,1429742482,194194093);

 system.token(pkey)   | pkey       | col1
----------------------+------------+------
 -9222990752061244013 |  194194093 |    3
 -9222951081251632075 | 1429742482 |    2
 -5356761936623586000 | 3016254749 |    1

It is not possible to change this ordering. Cheers!

Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.