Using a different table to answer each query is the correct answer here assuming there's a significant amount of data.
If you don't have that much data, maybe you should consider using a database like Postgres which gives you query flexibility instead of horizontal scalability. On Sun, Jul 23, 2017 at 1:10 PM techpyaasa . <techpya...@gmail.com> wrote: > Hi vladyu/varunbarala > > Instead of creating second table as you said can I just have one(first) > table below and get all rows with status=0. > > CREATE TABLE IF NOT EXISTS test.user ( account_id bigint, pid bigint, > disp_name text, status int, PRIMARY KEY (account_id, pid) ) WITH CLUSTERING > ORDER BY (pid ASC); >> > > I mean get all rows within same partition(account_id) whose status=0(say some > value) using *UDF/UDA* in c* ? > >> >> select group_by_status from test.user; > > > where group_by_status is UDA/UDF > > > Thanks in advance > TechPyaasa > > > On Sun, Jul 23, 2017 at 10:42 PM, Vladimir Yudovin <vla...@winguzone.com> > wrote: > >> Hi, >> >> unfortunately ORDER BY is supported for clustering columns only... >> >> *Winguzone <https://winguzone.com?from=list> - Cloud Cassandra Hosting* >> >> >> ---- On Sun, 23 Jul 2017 12:49:36 -0400 *techpyaasa . >> <techpya...@gmail.com <techpya...@gmail.com>>* wrote ---- >> >> Hi Varun, >> >> Thanks a lot for your reply. >> >> In this case if I want to update status(status can be updated for given >> account_id, pid) , I need to delete existing row in 2nd table & add new >> one... :( :( >> >> Its like hitting cassandra twice for 1 change.. :( >> >> >> >> On Sun, Jul 23, 2017 at 8:42 PM, Varun Barala <varunbaral...@gmail.com> >> wrote: >> >> Hi, >> You can create pseudo index table. >> >> IMO, structure can be:- >> >> >> CREATE TABLE IF NOT EXISTS test.user ( account_id bigint, pid bigint, >> disp_name text, status int, PRIMARY KEY (account_id, pid) ) WITH CLUSTERING >> ORDER BY (pid ASC); >> CREATE TABLE IF NOT EXISTS test.user_index ( account_id bigint, pid bigint, >> disp_name text, status int, PRIMARY KEY ((account_id, status), disp_name) ) >> WITH CLUSTERING ORDER BY (disp_name ASC); >> >> to support query *:- select * from site24x7.wm_current_status where >> uid=1 order by dispName asc;* >> You can use *in condition* on last partition key *status *in table >> *test.user_index.* >> >> >> *It depends on your use case and amount of data as well. It can be >> optimized more...* >> Thanks!! >> >> On Sun, Jul 23, 2017 at 2:48 AM, techpyaasa . <techpya...@gmail.com> >> wrote: >> >> Hi , >> >> We have a table like below : >> >> CREATE TABLE ks.cf ( accountId bigint, pid bigint, dispName text, status >> int, PRIMARY KEY (accountId, pid) ) WITH CLUSTERING ORDER BY (pid ASC); >> >> >> >> We would like to have following queries possible on the above table: >> >> select * from site24x7.wm_current_status where uid=1 and mid=1; >> select * from site24x7.wm_current_status where uid=1 order by dispName >> asc; >> select * from site24x7.wm_current_status where uid=1 and status=0 order >> by dispName asc; >> >> I know first query is possible by default , but I want the last 2 queries >> also to work. >> >> So can some one please let me know how can I achieve the same in >> cassandra(c*-2.1.17). I'm ok with applying indexes etc, >> >> Thanks >> TechPyaasa >> >> >> >
