Query Help - Top Ranked ID based on Hours (Self Join?)

• tempdownloads

  SSC Enthusiast

  Points: 189

  More actions

  October 26, 2018 at 12:06 am

  #378061

  Can someone  PLEASE help

  I wrote a query to pull the top athleteID by date in a table (based on the number of workout_hours):

  Here's what I've been working on:

  
  SELECT w.* FROM workout_table AS w
  LEFT JOIN
  workout_table AS w2 ON w.athleteID= w2.athleteID
  AND
  w.workout_hours <= w2.workout_hours
  GROUP BY w.athleteID
  HAVING COUNT(w.athleteID) <= 1
  ORDER BY w.athleteID, w.workout_hours DESC;
  

  Here are the tables:

  
  CREATE TABLE workout_table (date_ VARCHAR(4), athleteID VARCHAR(3), workout_hours INT);
  INSERT INTO workout_table VALUES ('12/3','201','54');
  INSERT INTO workout_table VALUES ('12/4','203','19');
  INSERT INTO workout_table VALUES ('12/7','201','32');
  
  
  
  
  
  CREATE TABLE date_table (date_ VARCHAR(4));
  INSERT INTO date_table VALUES ('12/1');
  INSERT INTO date_table VALUES ('12/2');
  INSERT INTO date_table VALUES ('12/3');
  INSERT INTO date_table VALUES ('12/4');
  INSERT INTO date_table VALUES ('12/5');
  INSERT INTO date_table VALUES ('12/6');

  Can someone please help?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 26, 2018 at 1:35 am

  #2011295

  What is your expected output here?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 26, 2018 at 2:14 am

  #2011301

  tempdownloads - Friday, October 26, 2018 12:06 AM

  Can someone  PLEASE help

  I wrote a query to pull the top athleteID by date in a table (based on the number of workout_hours):

  Here's what I've been working on:

  
  SELECT w.* FROM workout_table AS w
  LEFT JOIN
  workout_table AS w2 ON w.athleteID= w2.athleteID
  AND
  w.workout_hours <= w2.workout_hours
  GROUP BY w.athleteID
  HAVING COUNT(w.athleteID) <= 1
  ORDER BY w.athleteID, w.workout_hours DESC;
  

  Here are the tables:

  
  CREATE TABLE workout_table (date_ VARCHAR(4), athleteID VARCHAR(3), workout_hours INT);
  INSERT INTO workout_table VALUES ('12/3','201','54');
  INSERT INTO workout_table VALUES ('12/4','203','19');
  INSERT INTO workout_table VALUES ('12/7','201','32');
  
  
  
  
  
  CREATE TABLE date_table (date_ VARCHAR(4));
  INSERT INTO date_table VALUES ('12/1');
  INSERT INTO date_table VALUES ('12/2');
  INSERT INTO date_table VALUES ('12/3');
  INSERT INTO date_table VALUES ('12/4');
  INSERT INTO date_table VALUES ('12/5');
  INSERT INTO date_table VALUES ('12/6');

  Can someone please help?

  Is below results is your requirement?

  
CREATE TABLE workout_table (date_ VARCHAR(4), athleteID VARCHAR(3), workout_hours INT);
INSERT INTO workout_table VALUES ('12/3','201','54');
INSERT INTO workout_table VALUES ('12/3','202','84');
INSERT INTO workout_table VALUES ('12/4','203','19');
INSERT INTO workout_table VALUES ('12/7','201','32');
select date_,athleteID,workout_hours from
(
select date_,athleteID,workout_hours,
max_workout_hours=row_number() over (partition by date_ order by workout_hours desc)
from workout_table
)cal
where max_workout_hours=1


  Saravanan