CODEWITHSUNDEEP
cassandracqlcomposite-key
Vigneshwaran
Vigneshwaran

Reputation: 3275

How to apply limit for each unique composite column value in cassandra?

I have a sample table structure like this:

CREATE TABLE testcomposite ( day text, name text, lpt varint, details text, PRIMARY KEY (day, name, lpt) )

and I have the data like this:

cqlsh:KS> select * from testcomposite;

day | name | lpt | details ------+---------+---------+-------- day1 | name1 | 10 | abcdef day1 | name1 | 11 | abcdef day1 | name1 | 21 | abcdef day1 | name2 | 10 | abcdef day1 | name2 | 11 | abcdef

Is it possible to query to get a result like this where each row contains unique name field with their highest lpt value?

day | name | lpt | details ------+---------+---------+-------- day1 | name1 | 21 | abcdef day1 | name2 | 11 | abcdef

Upvotes: 0

Views: 56

Answers (1)

Arun Joy Thekkiniyath
Arun Joy Thekkiniyath

Reputation: 884

    CREATE FUNCTION state_group_and_max( state map<text, int>, type text, amount int )
    CALLED ON NULL INPUT
    RETURNS map<text, int>
    LANGUAGE java AS '
    Integer count = (Integer) state.get(type);  if (count == null) count = amount; else count = Math.max(count, amount); state.put(type, count); return state; ' ;


    CREATE OR REPLACE AGGREGATE group_and_max(text, int) 
    SFUNC state_group_and_max 
    STYPE map<text, int> 
    INITCOND {};

cqlsh:test> select group_and_max(name,lpt) from testcomposite where day = 'day1';

 test.group_and_max(name, lpt)
-------------------------------
    {'name1': 21, 'name2': 11}

Upvotes: 1

Related Questions