rank_by_year_and_name

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

DROP TABLE IF EXISTS cycling.rank_by_year_and_name;

// Store race information by year and race name using a COMPOSITE PARTITION KEY

// tag::use_and_compositepk[]
USE cycling;
CREATE TABLE rank_by_year_and_name ( 
  race_year int, 
  race_name text, 
  cyclist_name text, 
  rank int, 
  PRIMARY KEY ((race_year, race_name), rank) 
);
// end::use_and_compositepk[]

DROP TABLE IF EXISTS cycling.rank_by_year_and_name;

// tag::compositepk[]
CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);
// end::compositepk[]

// tag::dropindex[]
DROP INDEX IF EXISTS cycling.rank_idx;
// end::dropindex[]

// tag::createindex[]
CREATE INDEX IF NOT EXISTS rank_idx
ON cycling.rank_by_year_and_name (rank);
// end::createindex[]

// tag::drop_race_year_idx[]
DROP INDEX IF EXISTS cycling.race_year_idx;
// end::drop_race_year_idx[]

// tag::create_race_year_idx[]
CREATE INDEX IF NOT EXISTS race_year_idx ON
cycling.rank_by_year_and_name (race_year);
// end::create_race_year_idx[]

INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 1);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Adam PHELAN', 2);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Thomas LEBAS', 3);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 3);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Daniel MARTIN', 1);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Johan Esteban CHAVES', 2);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Ilnur ZAKARIN', 1);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Carlos BETANCUR', 2);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Daniel MARTIN', 2);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Daniel MARTIN', 2);
INSERT INTO cycling.rank_by_year_and_name (race_year, race_name, cyclist_name, rank) VALUES (2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3);

// Show all inserted data
CAPTURE 'select_all_from_rank_by_year_and_name.results';
SELECT *
FROM cycling.rank_by_year_and_name;
CAPTURE OFF;

CAPTURE 'select_all_from_rank_by_year_and_name_Japan_2014.results';
// tag::select_with_name_and_year[]
SELECT *
FROM cycling.rank_by_year_and_name
WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';
// end::select_with_name_and_year[]
CAPTURE OFF;

// tag::sepstatementswithand[]
SELECT
  rank,
  cyclist_name AS name
FROM cycling.rank_by_year_and_name
WHERE "race_name"  = 'Tour of Japan - Stage 4 - Minami > Shinshu' 
  AND race_year = 2014;
// end::sepstatementswithand[]

// tag::columnalias[]
CAPTURE 'select_best_rank_from_rank_by_year_and_name.results';
SELECT
  MIN(rank) AS best_rank,
  cyclist_name
FROM cycling.rank_by_year_and_name
WHERE "race_name" = 'Tour of Japan - Stage 4 - Minami > Shinshu' 
  AND race_year = 2014;
// end::columnalias[]
CAPTURE OFF;

// The following query generates a warning, which is normal
// tag::countrows[]
SELECT COUNT(*)
FROM cycling.rank_by_year_and_name;
// end::countrows[]

// tag::limitrows[]
SELECT cyclist_name
FROM cycling.rank_by_year_and_name
LIMIT 50000;
// end::limitrows[]

// Query by partition
CAPTURE 'select_all_from_rank_by_year_and_name_partition_limit.results';
// tag::partlimit[]
SELECT * 
FROM cycling.rank_by_year_and_name 
PER PARTITION LIMIT 2;
// end::partlimit[]
CAPTURE OFF;

// Select rank - filter on a clustering column 
CAPTURE 'select_all_from_rank_by_year_and_name_rank_1.results';
// tag::selectrank[]
SELECT *
FROM cycling.rank_by_year_and_name
WHERE rank = 1;
// end::selectrank[]
CAPTURE OFF;

CAPTURE 'select_all_from_rank_by_year_and_name_2014.results';
// tag::select_with_year[]
SELECT *
FROM cycling.rank_by_year_and_name
WHERE race_year = 2014;
// end::select_with_year[]
CAPTURE OFF;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com