October 26, 2018 at 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?
October 26, 2018 at 2:14 am
tempdownloads - Friday, October 26, 2018 12:06 AMCan 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply