Order by is currently only supported on the clustered columns of the PRIMARY KEY

Question

cassandra2.0.7 cql 3.1.1

CREATE TABLE playlists (
  id uuid,
  song_order int,
  song_id uuid,
  title text,
  album text,
  artist text,
  PRIMARY KEY  (id, song_order ) );


INSERT INTO playlists (id, song_order, song_id, title, artist, album)
  VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 1,
  a3e64f8f-bd44-4f28-b8d9-6938726e34d4, 'La Grange', 'ZZ Top', 'Tres Hombres');

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
  VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 2,
  8a172618-b121-4136-bb10-f665cfc469eb, 'Moving in Stereo', 'Fu Manchu', 'We Must Obey');

INSERT INTO playlists (id, song_order, song_id, title, artist, album)
  VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 3,
  2b09185b-fb5a-4734-9b56-49077de9edbf, 'Outside Woman Blues', 'Back Door Slam', 'Roll Away');


  SELECT * FROM playlists WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204  
  ORDER BY song_order DESC LIMIT 2;

error: Order by is currently only supported on the clustered columns
   of the PRIMARY KEY, got song_order

this demo from : http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/select_r.html

who can tell me why ? thanks!

Answer 1

Try this when you create your table.

CREATE TABLE playlists ( 
    id uuid, song_order int, song_id uuid, title text, album text, artist text, 
    PRIMARY KEY (id, song_order)) WITH CLUSTERING ORDER BY (song_order ASC);