question

preethikoluvaiya23_193353 avatar image
preethikoluvaiya23_193353 asked erjcan commented

How do I query a table with clustering column in the primary key?

Created table with primary key tag clustering column and order by(added_date desc) clause, but still I don't find my result as per query.Please clarify my doubt

cql
error.jpg (55.8 KiB)
1 comment
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.

bettina.swynnerton avatar image bettina.swynnerton ♦♦ commented ·

Hi,

to be sure we understand your issue, would you update your post with the query that fails for you with this table schema?

Thanks!

0 Likes 0 ·

1 Answer

bettina.swynnerton avatar image
bettina.swynnerton answered erjcan commented

Hi @preethikoluvaiya23_193353,

I went through the exercise 4 in dc201 to see what queries you might have tried with this table.

Here is your table:

KVUser@cqlsh:killrvideo> select * from videos_by_tag;

 tag       | added_date                      | video_id                             | title
-----------+---------------------------------+--------------------------------------+------------------------------
  datastax | 2013-10-16 00:00:00.000000+0000 | 4845ed97-14bd-11e5-8a40-8338255b7e33 |              DataStax Studio
  datastax | 2013-04-16 00:00:00.000000+0000 | 5645f8bd-14bd-11e5-af1a-8638355b8e3a | What is DataStax Enterprise?
 cassandra | 2014-01-29 00:00:00.000000+0000 | 1645ea59-14bd-11e5-a993-8138354b7e31 |            Cassandra History
 cassandra | 2013-03-17 00:00:00.000000+0000 | 3452f7de-14bd-11e5-855e-8738355b7e3a |              Cassandra Intro
 cassandra | 2012-04-03 00:00:00.000000+0000 | 245e8024-14bd-11e5-9743-8238356b7e32 |             Cassandra & SSDs

(5 rows)

The following query can be run, because both the partition key and the clustering key are specified in the query:

KVUser@cqlsh:killrvideo> SELECT *
              ... FROM videos_by_tag
              ... WHERE tag = 'cassandra' and added_date >= '2013-1-1'
              ... ORDER BY added_date ASC;

 tag       | added_date                      | video_id                             | title
-----------+---------------------------------+--------------------------------------+-------------------
 cassandra | 2013-03-17 00:00:00.000000+0000 | 3452f7de-14bd-11e5-855e-8738355b7e3a |   Cassandra Intro
 cassandra | 2014-01-29 00:00:00.000000+0000 | 1645ea59-14bd-11e5-a993-8138354b7e31 | Cassandra History

(2 rows)

The following query however fails, because the partition key ("tag") is omitted:

KVUser@cqlsh:killrvideo> SELECT *
              ... FROM videos_by_tag
              ... ORDER BY added_date ASC;
InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."

I hope this helps you continue with this exercise.

Cheers!

1 comment 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.

erjcan avatar image erjcan commented ·

hi , i dont wanna create separate topic, but i m doing same exercise and for step 6 - i can't do it.


the query i used to create table:

create table videos_by_tag( tag text, video_id uuid,  added_date timestamp , title text , primary key (tag, added_date,video_id)) with clustering order by (added_date desc);

then copy data into it.

step5 - list all data:
select * from videos_by_tag;


step 6 :Execute your query again, but list the oldest videos first

this will not work, because we did not specify 'tag' :

select * from videos_by_tag order by added_date asc;


but i need to list all videos just in diff.order - what do i need to put in where clause ?


i tried this:


select * from videos_by_tag where tag in ('cassandra', 'datastax') order by added_date asc;

and this:


select * from videos_by_tag where tag = 'cassandra' or tag = 'datastax' order by added_date asc;

still no success, how to list all videos in oldest order ?

0 Likes 0 ·