getting result in the same order it was added in the table

Question

I have this table

CREATE TABLE tag_by_user (
userId uuid,
tagId uuid,
colId timeuuid,
tagLabel text,
PRIMARY KEY (userId, tagId,colId)
);

here is my data

insert into tag_by_user(userId,tagId,colId,tagLabel) values(4978f728-0f96-11e5-a6c0-1697f925ec7b
,b0b328fa-0f96-11e5-a6c0-1697f925ec7b,now(),'html');
insert into tag_by_user(userId,tagId,colId,tagLabel) values(4978f728-0f96-11e5-a6c0-1697f925ec7b
,b0b330d4-0f96-11e5-a6c0-1697f925ec7b,now(),'java');
insert into tag_by_user(userId,tagId,colId,tagLabel) values(4978f728-0f96-11e5-a6c0-1697f925ec7b
,c0f22450-0f96-11e5-a6c0-1697f925ec7b,now(),'javascript');
insert into tag_by_user(userId,tagId,colId,tagLabel) values(4978f728-0f96-11e5-a6c0-1697f925ec7b
,c0f226b2-0f96-11e5-a6c0-1697f925ec7b,now(),'scala pro');
insert into tag_by_user(userId,tagId,colId,tagLabel) values(4978f728-0f96-11e5-a6c0-1697f925ec7b
,c0f22ab8-0f96-11e5-a6c0-1697f925ec7b,now(),'c++');

Now i want to get the tags of a given user in same order it was added to the row (i.e in the ascending order of time when it was added and here that one is colId)

cqlsh:ks_demo> select taglabel from tag_by_user where userid= 77c4d46c-0f96-11e5-a6c0-1697f925ec7b  order by colid;

it gives this error

Bad Request: Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY

What changes i will have to in schema or in query cqlsh 4.1.1 | Cassandra 2.0.8

Answer 1

You need to leave only userId and colId in the PRIMARY KEY:

CREATE TABLE tag_by_user (
userId uuid,
colId timeuuid,
tagId uuid,
tagLabel text,
PRIMARY KEY (userId, colId)
);

And then use

SELECT * FROM tag_by_user WHERE userId={yourUserId}

to get the tags of a given user in ascending order of time.

If you need to avoid duplicate tags, then you can create an index on tagId and use it to find out if a tag already exists for a given user and process it. Though you cannot modify colId once data is inserted.

Answer 2

As the message suggests, to use order by, you should follow the same order as in PRIMARY KEY.

CREATE TABLE tag_by_user (
    userid uuid,
    colid timeuuid,
    tagid uuid,
    taglabel text,
    PRIMARY KEY (userid, colid, tagid)
);

select taglabel from tag_by_user where userid = 4978f728-0f96-11e5-a6c0-1697f925ec7b  order by colid;

 taglabel
------------
       html
       java
 javascript
  scala pro
        c++