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 > > >