Re: CQL get unique row keys ?

2014-02-15 Thread DuyHai Doan 
Hello Andy

 Since C* 2.0.1 it is possible to list all distinct partition keys (not
clustering keys) with: SELECT DISTINCT pk FROM 

 More info on the feature here:
https://issues.apache.org/jira/browse/CASSANDRA-4536

 This query is quite efficient because the key cache is used most of the
time.

 Regards

 Duy Hai DOAN



On Sat, Feb 15, 2014 at 6:05 PM, Andrew Cobley a.e.cob...@dundee.ac.ukwrote:

  I may be missing something here, but is there a way in cql to get all
 unique row keys in a column family(table) ?

  I've created a table like this:

   CREATE TABLE totp (
  artist varchar,
  track varchar,
   appearance_type varchar,
   PRIMARY KEY ((artist),track)
  ) WITH CLUSTERING ORDER BY (track asc);

  and populated it with data.  As you can imagine, each artist can have
 multiple tracks .  Doing:

  Select * from totp; will produce something like (with more artists of
 course)

 *The Tony Rich Project* | 
 *Nobody
 Knows* | *P/A*
 *Telly Savalas* |
   *If* |   *PROMO*
  *Alice Cooper* |
   *Elected* |*DISC*
  *Alice Cooper* | 
 *Hello
 Hooray* |   *PROMO*
  *Alice Cooper* |
   *Hey Stupid* |   *Promo*
  *Alice Cooper* |  *Lost
 In America* | *P/A*
  *Alice Cooper* |  *No
 More Mr.Nice Guy* |*DISC*
  *Alice Cooper* |
   *Poison* |   *Promo*
  *Alice Cooper* | 
 *School's
 Out* | *P/A V/T*
  *Alice Cooper* |
   *Schools Out* | *P/A*
  *Alice Cooper* |   *Teenage
 Lament '74* |*DISC*
   *Blackstreet Feat Dr.Dre* |
   *No Diggity* | *P/A*
  *Nena* |   *99
 Luftballons* |   *Promo*
  *Nena* |  *99
 Red Balloons* | *P/A*
  *and Select artist from totp; would produce:*

   *The Tony Rich Project*
 *Telly Savalas*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
  *Alice Cooper*
*Blackstreet Feat Dr.Dre*
  *Nena*
  *Nena*

  *But of course what I really want is just the row keys *
   *The Tony Rich Project*
 *Telly Savalas*
  *Alice Cooper*
*Blackstreet Feat Dr.Dre*
  *Nena*

  *Any idea if I can do this in CQL ?*

  *Andy*


 The University of Dundee is a registered Scottish Charity, No: SC015096

RE: CQL get unique row keys ?

2014-02-15 Thread Andrew Cobley 
Thanks,


worked a treat !

Andy



From: DuyHai Doan doanduy...@gmail.com
Sent: 15 February 2014 18:51
To: user@cassandra.apache.org
Subject: Re: CQL get unique row keys ?

Hello Andy

 Since C* 2.0.1 it is possible to list all distinct partition keys (not 
clustering keys) with: SELECT DISTINCT pk FROM 

 More info on the feature here: 
https://issues.apache.org/jira/browse/CASSANDRA-4536

 This query is quite efficient because the key cache is used most of the time.

 Regards

 Duy Hai DOAN



On Sat, Feb 15, 2014 at 6:05 PM, Andrew Cobley 
a.e.cob...@dundee.ac.ukmailto:a.e.cob...@dundee.ac.uk wrote:
I may be missing something here, but is there a way in cql to get all unique 
row keys in a column family(table) ?

I've created a table like this:

CREATE TABLE totp (
artist varchar,
track varchar,
 appearance_type varchar,
 PRIMARY KEY ((artist),track)
) WITH CLUSTERING ORDER BY (track asc);

and populated it with data.  As you can imagine, each artist can have multiple 
tracks .  Doing:

Select * from totp; will produce something like (with more artists of course)

   The Tony Rich Project | Nobody 
Knows | P/A
Telly Savalas | 
  If |   PROMO
 Alice Cooper |  
Elected |DISC
 Alice Cooper | Hello 
Hooray |   PROMO
 Alice Cooper |   Hey 
Stupid |   Promo
 Alice Cooper |  Lost In 
America | P/A
 Alice Cooper |  No More 
Mr.Nice Guy |DISC
 Alice Cooper |   
Poison |   Promo
 Alice Cooper | 
School's Out | P/A V/T
 Alice Cooper |  
Schools Out | P/A
 Alice Cooper |   Teenage 
Lament '74 |DISC
  Blackstreet Feat Dr.Dre |   No 
Diggity | P/A
 Nena |   99 
Luftballons |   Promo
 Nena |  99 Red 
Balloons | P/A
and Select artist from totp; would produce:

The Tony Rich Project
Telly Savalas
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
 Alice Cooper
  Blackstreet Feat Dr.Dre
 Nena
 Nena

But of course what I really want is just the row keys
The Tony Rich Project
Telly Savalas
 Alice Cooper
  Blackstreet Feat Dr.Dre
 Nena

Any idea if I can do this in CQL ?

Andy


The University of Dundee is a registered Scottish Charity, No: SC015096


The University of Dundee is a registered Scottish Charity, No: SC015096