get all rows where column value is same in cassandra cql

Question

This is my table.

cqlsh:sachhya> select * FROM emp;

 emp_id | age | emp_name     | exp | mobile
--------+-----+--------------+-----+------------
      5 |  29 | RAHUL SHARMA |   9 | 2312343123
      1 |  24 |      SACHHYA |  15 | 9090987876
      2 |  14 |      SACHHYA |  15 | 9090987876
      4 |  22 |        ANKUR |  32 | 3213456321
     90 |  30 |       sumeet |   2 |   91234212
      3 |  14 |      SACHHYA |   3 | 9090987876

PRIMARY KEY (Partition key) IS emp_id.

I want to display all rows where emp_name is 'SACHHYA'. What command should i use?

Below is the cql query that i am using.

select * FROM emp WHERE emp_name='SACHHYA';

But i am getting an error:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Predicates on non-primary-key columns (emp_name) are not yet supported for non secondary index queries"

I think that this explains your issue stackoverflow.com/questions/19231778/… — Horia
change table so PRIMARY KEY (emp_name, emp_id) and you can — Chris Lohfink
@Horia and Chris Lohfink thanks for your valuable time and link. — Sachhya

Sachhya Sachhya · Accepted Answer · 2018-04-18T06:26:43

I have found one solution for my question, We can crate index on 'emp_name' column after that we can use 'emp_name' filter.

EX:

 CREATE INDEX NameIndx ON emp (emp_name);
 SELECT * from sachhya.emp WHERE emp_name = 'SACHHYA';

My output:

emp_id | age | desegnation      | emp_name | exp | mobile
--------+-----+------------------+----------+-----+------------
    711 |  22 | Trainee Engineer |  SACHHYA |   1 | 9232189345
      2 |  24 |         Engineer |  SACHHYA |   3 | 9033864540

My Table:

 emp_id | age | desegnation      | emp_name | exp  | mobile
--------+-----+------------------+----------+------+------------
      5 |  29 |   Technical Lead |    RAHUL |    9 | 2312343123
     10 |  45 | Deleviry Manager |   ANDREW |   22 | 9214569345
    711 |  22 | Trainee Engineer |  SACHHYA |    1 | 9232189345
      2 |  24 |         Engineer |  SACHHYA |    3 | 9033864540
      4 |  26 |         Engineer |    ANKUR |    3 | 3213456321
     22 |  20 |           Intern |      SAM | null | 8858699345
      7 |  22 | Trainee Engineer |    JACOB |    1 | 9232189345
     17 |  28 |  Senior Engineer |     JACK |    4 | 8890341799
     90 |  30 |  Senior Engineer | HERCULES |    6 | 9353405163
      3 |  32 |   Technical Lead |     ROSS |    8 | 7876561355

get all rows where column value is same in cassandra cql

1 Answers