table-rank-by-year-and-name

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);

CREATE INDEX IF NOT EXISTS cycling.race_name_idx
  ON cycling.rank_by_year_and_name (race_name);

CREATE CUSTOM INDEX IF NOT EXISTS race_name_idx
  ON cycling.rank_by_year_and_name (race_name) USING 'StorageAttachedIndex';

CREATE CUSTOM INDEX race_name_sasi_idx ON cycling.rank_by_year_and_name (race_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';

CREATE INDEX IF NOT EXISTS cycling.race_year_idx
  ON cycling.rank_by_year_and_name (race_year);

CREATE CUSTOM INDEX IF NOT EXISTS race_year_idx
ON cycling.rank_by_year_and_name (race_year) USING 'StorageAttachedIndex';

CREATE CUSTOM INDEX race_year_sasi_idx ON cycling.rank_by_year_and_name (race_year)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';

CREATE INDEX IF NOT EXISTS cycling.rank_idx
  ON cycling.rank_by_year_and_name (rank);

CREATE CUSTOM INDEX IF NOT EXISTS rank_idx
ON cycling.rank_by_year_and_name (rank) USING 'StorageAttachedIndex';

CREATE CUSTOM INDEX rank_sasi_idx ON cycling.rank_by_year_and_name (rank)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';

DESCRIBE INDEX cycling.race_name_idx;

DESCRIBE INDEX cycling.race_name_idx;

DESCRIBE INDEX cycling.race_year_idx;

DESCRIBE INDEX cycling.race_year_idx;

DESCRIBE INDEX cycling.rank_idx;

DESCRIBE INDEX cycling.rank_idx;

DESCRIBE INDEX cycling.rank_sasi_idx;

DROP INDEX IF EXISTS cycling.race_name_idx;

DROP INDEX IF EXISTS cycling.race_year_idx;

DROP INDEX IF EXISTS cycling.rank_idx;

SELECT * FROM cycling.rank_by_year_and_name;

SELECT cyclist_name FROM cycling.rank_by_year_and_name WHERE rank = 1;

SELECT * FROM cycling.rank_by_year_and_name
    WHERE race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu';

SELECT * FROM cycling.rank_by_year_and_name
    WHERE race_year = 2014;

SELECT COUNT(*) FROM cycling.rank_by_year_and_name;

SELECT cyclist_name FROM cycling.rank_by_year_and_name
  WHERE race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
    AND race_year = 2015
    AND rank <= 2;

SELECT cyclist_name FROM cycling.rank_by_year_and_name LIMIT 50000;

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;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014
  AND race_name  = 'Tour of Japan - Stage 4 - Minami > Shinshu';

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3
ALLOW FILTERING;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE race_year = 2014 AND rank = 3;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank = 3;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank = 3;

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;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank < 3;

SELECT * FROM cycling.rank_by_year_and_name
  WHERE rank < 3;

SELECT race_name AS name,race_year AS year FROM cycling.rank_by_year_and_name
  WHERE TOKEN(race_year,race_name) >= 4582455970709790046;

SELECT TOKEN(race_year,race_name) AS tokens,race_name AS name,race_year AS year
  FROM cycling.rank_by_year_and_name
  PER PARTITION LIMIT 1;

SELECT TOKEN(race_year, race_name),race_name AS name,race_year AS year
  FROM cycling.rank_by_year_and_name
  WHERE TOKEN(race_year, race_name) >= -3074457345618258603
    AND TOKEN(race_year, race_name) <= 3074457345618258602;

SELECT cyclist_name FROM cycling.rank_by_year_and_name
  WHERE race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
    AND race_year IN (2014, 2015);

SELECT rank, cyclist_name AS name FROM cycling.rank_by_year_and_name
  PER PARTITION LIMIT 2;

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)
);

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)
);

DESCRIBE TABLE cycling.rank_by_year_and_name;

DROP TABLE IF EXISTS cycling.rank_by_year_and_name;

table-rank-by-year-and-name

Was this helpful?

Give Feedback