CODEWITHSUNDEEP
cassandranosql
DJ_R
DJ_R

Reputation: 207

Is it possible to select X records from every clustering key Y in a partition?

Given a table with PRIMARY KEY (pkey, ckey_a, ckey_b, etc) WITH CLUSTERING ORDER BY (ckey_a, ckey_b, etc), is it possible to make a select statement to get the first 30 records of every ckey_b grouping from a specific primary key/wide row?

Upvotes: 1

Views: 34

Answers (2)

Chris Lohfink
Chris Lohfink

Reputation: 16400

You can do a SELECT * FROM table GROUP BY pkey, ckey_a, ckey_b to get the unique and ckey_c's and such from ckey_b. You can limit by partition (see alex's answer) but you cannot currently limit by the group. If your query isnt called often (this is expensive) you can create a UDA that combines with the GROUP BY to limit the number by the group, throwing away the rest. This is very expensive though since the coordinator still gets all the values, just filters them out before sending back to client.

Upvotes: 2

Alex Ott
Alex Ott

Reputation: 87099

Cassandra supports limiting on number of results per partition only using the SELECT * FROM ks.table PER PARTITION LIMIT N; syntax. But you can achieve what you need if you switch from PRIMARY KEY (pkey, ckey_a, ckey_b) to PRIMARY KEY ((pkey, ckey_a), ckey_b) for example, but this depends on your queries.

Upvotes: 2

Related Questions