Finding different non-primary key column values ​​in CQL Cassandra

Question

I use the following code to create a table:

CREATE KEYSPACE mykeyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
USE mykeyspace;
CREATE TABLE users (
  user_id int PRIMARY KEY,
  fname text,
  lname text
);
INSERT INTO users (user_id,  fname, lname)
  VALUES (1745, 'john', 'smith');
INSERT INTO users (user_id,  fname, lname)
  VALUES (1744, 'john', 'doe');
INSERT INTO users (user_id,  fname, lname)
  VALUES (1746, 'john', 'smith');

I would like to find an excellent column value lname(this is not the PRIMARY KEY). I would like to get the following result:

 lname
-------
 smith

Using SELECT DISTINCT lname FROM users; However, since lnameit is not PRIMARY KEY, I get the following error:

InvalidRequest: code=2200 [Invalid query] message="SELECT DISTINCT queries must
only request partition key columns and/or static columns (not lname)"
cqlsh:mykeyspace> SELECT DISTINCT lname FROM users;

How to get individual values ​​from lname?

Answer 1

The user - Undefined_variable - makes two good points:

• Cassandra , . , .
• DISTINCT .

, , , :

CREATE TABLE users_by_lname (
    lname text,
    fname text,
    user_id int,
    PRIMARY KEY (lname, fname, user_id)
);

, INSERT , :

aploetz@cqlsh:stackoverflow> SELECT DISTINCT lname FROm users_by_lname ;

 lname
-------
 smith
   doe

(2 rows)

: (lname) fname, fname . user_id , .

Answer 2

. DISTINCT . . ( )