Returning max value in multiple columns and adjacent column

Question

Query question on SQL Server 2014.

I've been having some trouble with a query to return the MAX values from various columns.

The current query I'm running is below, and this works fine to return the max value from the columns specified for each date.

SELECT ws.ExerciseID, 
ws.Date,
(SELECT Max(v) FROM 
(VALUES (WeightReps1), (WeightReps2), (WeightReps3), (WeightReps4), (WeightReps5)
   , (WeightReps6), (WeightReps7), (WeightReps8), (WeightReps9), (WeightReps10)
) 
AS value(v)
) 
as [Max Weight]
FROM workoutsessions ws
join ExerciseCultures ec on ws.ExerciseID = ec.ExerciseID
where ec.Name = 'Bench Press'

So this shows the max from these columns. Fantastic. However each of the columns 'weightreps' column has an adjacent column for 'Reps'. I need to return the max value from the columns as above, but only where the adjacent 'reps' column is greater or equal to a value.

I've provided a sample below:

ExerciseID | WorkoutID | Date       | WeightReps1 | Reps1 | Weightreps2 | Reps2 | Weightreps3 | Reps3
105        | 201      | 11/08/2014 | 50          | 9     | 65          | 3     | 75             | 1
 105       | 202      | 13/08/2014 | 55          | 6     | 70          | 2     | 77        | 1

So in the above sample, if I only want to return the highest value where the 'reps' column is greater than two. I would return the value '70' as this is the highest value where the adjacent reps column is equal or greater than two.

I've tried the below code, but this will bring back the highest value where any of the reps columns are greater than two -- not helpful.

SELECT top 1 ec.Name, Date,
  (SELECT Max(v) 
   FROM (VALUES (WeightReps1), (WeightReps2), (WeightReps3), (WeightReps4), (WeightReps5)
   , (WeightReps6), (WeightReps7), (WeightReps8), (WeightReps9), (WeightReps10)
   ) AS value(v)
   ) as [Weight]
FROM workoutsessions ws
join ExerciseCultures ec on ws.ExerciseID = ec.ExerciseID
where 
ec.Name = 'Bench Press'
and ws.reps1 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps2 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps3 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps4 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps5 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps6 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps7 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps8 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps9 >= '6'
or
ec.Name = 'Bench Press'
and ws.reps10 >= '6'
order by weight desc

Any ideas where I'm going wrong?

Thanks.

If your base table was normalized this would be simple. Any chance you can fix the table? That will not only fix this issue but many other challenges down the road. — Sean Lange
– Sean Lange, Commented Feb 18, 2015 at 15:59
I forgot to include a column for 'WorkoutID' which is included for each row. The table only allows for one WorkoutID and ExerciseID to be used in conjunction. — Adam
– Adam, Commented Feb 18, 2015 at 16:11

Andriy M · Accepted Answer · 2015-02-18 18:56:14Z

2

You could just change this subquery:

SELECT Max(v)
FROM 
  (
    VALUES
      (WeightReps1), (WeightReps2), (WeightReps3), (WeightReps4), (WeightReps5),
      (WeightReps6), (WeightReps7), (WeightReps8), (WeightReps9), (WeightReps10)
  ) AS value(v)

to this:

SELECT Max(w)
FROM 
  (
    VALUES
      (WeightReps1, Reps1), (WeightReps2, Reps2),
      (WeightReps3, Reps3), (WeightReps4, Reps4),
      (WeightReps5, Reps5), (WeightReps6, Reps6),
      (WeightReps7, Reps7), (WeightReps8, Reps8),
      (WeightReps9, Reps9), (WeightReps10, Reps10)
  ) AS value(w, r)
WHERE
  r >= ... /* some value */

answered Feb 18, 2015 at 18:56

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giorgos Betsos Over a year ago

Cleaner, better than mine.

Giorgos Betsos · Accepted Answer · 2015-02-18 17:13:31Z

1

You can use a CASE expression inside the VALUES clause:

SELECT ws.ExerciseID, 
ws.Date,
(SELECT Max(v) FROM 
(VALUES (CASE WHEN Reps1 > 6 THEN WeightReps1 ELSE NULL END), 
        (CASE WHEN Reps2 > 6 THEN WeightReps2 ELSE NULL END), 
        (CASE WHEN Reps3 > 6 THEN WeightReps3 ELSE NULL END), 
            ... etc
) 
AS value(v)
) 
as [Max Weight]
FROM workoutsessions ws
join ExerciseCultures ec on ws.ExerciseID = ec.ExerciseID
where ec.Name = 'Bench Press'

In this way WeightRepsx value will be ignored, if the 'adjacent' Repsx value is not greater than a specific value, as per your requirement.

This is rather ugly of course, but it is an option since your table is not properly normalized.

edited Feb 18, 2015 at 17:13

answered Feb 18, 2015 at 16:12

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

Returning max value in multiple columns and adjacent column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related